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?

By which method are you passing an arbitrary number of codes to your proc? e.g. table variable, XML, delimited list, multiple parameters defaulting to NULL, etc? Would you consider changing this method? — onedaywhen, Sep 17 '10 at 08:26

score 4 · Answer 1 · answered Sep 16 '10 at 19:55

4

where isnull(@ActivityCode, ActivityCode) = ActivityCode

answered Sep 16 '10 at 19:55

Denis Valeev

5,975
35
41

score 3 · Answer 2 · answered Sep 16 '10 at 19:54

3

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

answered Sep 16 '10 at 19:54

Cătălin Pitiș

14,123
2
39
62

score 2 · Accepted Answer · edited May 23 '17 at 12:19

2

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

edited May 23 '17 at 12:19

Community

1
1

answered Sep 16 '10 at 19:58

OMG Ponies

325,700
82
523
502

This is closest to what I was looking for, although I ended up using a function to dump the codes into a table and then selected the activity codes with an inner join. – ofm Sep 28 '10 at 18:39
Just for the record, because this confused me... The example stated as "nor sargable"... is. It's one of the examples used on many definitions of what is sargable (as opposed to the isnull() pattern), including the one that's linked to in this answer. (after 5 years, this is still appearing in search results :) ) – tr00st Apr 30 '15 at 14:42

Multiple WHERE clause inputs to T-SQL stored procedure

3 Answers3

SQL Server 2005+