Help With Optional Parameter Query With IN Statements

I have a query with 17 separate, optional, parameters. I have declared each parameter = NULL so that I can test for NULL in the case that the user didn€™t not pass in the parameter.

I am new enough to SQL Server that I am having difficulty building the WHERE clause with all of these optional parameters.

One solution I was advised on by a well paid SQL programmer, was to use a string in the stored proc and dynamically build the WHERE clause and exec it at the end of the sp. But the whole point of a stored proc is that it can be compiled and cached to make it faster, yet the string approach makes it have to compile every time it€™s run! Not a good solution, but maybe it€™s the best I can do . . .

I have tried many different approaches using different functions, etc. but I€™ve hit a brick wall. Any help in sorting it out with YOUR techniques would be greatly appreciated:

1. To add the parameter to the WHERE clause and test for NULL I€™ve used the COALESCE function such as €œWHERE table.fieldname = COALESCE(@Param, table.fieldname)€?. This works well if there is only one item in the parameter, but in the case that I pass multiple items to the parameter, it completely fails.

2. To handle multiple items, for example, if @Param = €˜3,7,98€™ (essentially, a csv separated list of keys)

Code SnippetWHERE table.fieldname IN(COALESCE(@Param, table.fieldname))


doesn€™t work because @Param needs to be parsed from a string into an array of integers in the parameter. So, I am using a UDF I discovered to parse the multi-item parameter. The UDF can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp and it returns a table variable that can be used in an IN statement. So I€™m using


Code SnippetISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@Param,€™,€™))



which works brilliantly in my WHERE statement AS LONG AS @Param ISN€™T NULL. So how do I test for NULL first and still use this approach to multi-item parameters?

I€™ve tried


Code SnippetWHERE @Param IS NULL OR ISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@Param,€™,€™))


and though it works, the OR causes it to slow way down as it compares every record for the OR. (It slows down by approximately 800%.) The other thing I tried was


Code SnippetISNULL (table.fieldname, 0) IN (CASE WHEN @Param IS NULL THEN ISNULL(table.fieldname, 0) ELSE (SELECT value FROM dbo.fn_Split(@Param,€™,€™)))



This fails with €œSubquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression€? due to the multiple values in the parameter. (I can€™t understand why the line without the CASE statement works, but the CASE line doesn€™t!)

Am I even on the right track, cuz this is driving me mad and I just need a way to deal with optional multi-item parameters in an IN statement? HELP!