Multiple WHERE clause inputs to T-SQL stored procedure

Question

I have a multiple selection listbox on a web form which lists various internal activity codes, and users can filter the results of a query based on the codes selected. I can easily add a single code as a parameter to my stored procedure and filter the results with a WHERE clause like so:

WHERE ActivityCode = @ActivityCode OR @ActivityCode is null 

but what is the best practise for handling multiple codes, especially when the number of codes selected is arbitrary?

Answer 1

You could use WHERE ActivityCode IN (@Code1, @Code2) OR ActivityCode IS NULL

Answer 2

where isnull(@ActivityCode, ActivityCode) = ActivityCode 

Answer 3

This:

WHERE ActivityCode = @ActivityCode OR @ActivityCode is null 

...works, but is not sargable.

If you only have one optional parameter, you can use an IF:

IF @ActivityCode IS NOT NULL
BEGIN

  SELECT ...
    FROM ...
   WHERE ActivityCode = @ActivityCode

END
ELSE
BEGIN

  SELECT ...
    FROM ...

END

FYI: SQL doesn't support a variable to represent a comma separated list - you have to use dynamic SQL for that.

SQL Server 2005+

DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = N'SELECT ...
                   FROM ... 
                  WHERE 1 = 1 '

    SET @SQL = @SQL + CASE 
                         WHEN @ActivityCode IS NOT NULL THEN ' AND ActivityCode IN (@ActivityCode) '
                         ELSE ' '
                      END

BEGIN

  EXEC sp_executesql @SQL, N'@ActivityCode VARCHAR(100)', @ActivityCode

END