Setting a variable to a list of strings for IN statement

Question

Can I assign a list of strings to a variable for use in an IN statement without using Dynamic SQL?

I am developing a report in SSRS which allows the user to select one of three possible values as a parameter which in turn will assign values associated with that choice to an IN statement. Some parameters have a single value associated and some have several.

Cases 1 and 2 run properly, but case 3 does not because it is passed as a scalar value instead of a list. I have tried various ways of escaping the quotes around each item, but it does not work.

declare @items as varchar(50)
set @items =
  CASE @parameter
    WHEN 1 then 'first'
    WHEN 2 then 'second'
    WHEN 3 then 'third, fourth'
  END

select column1
from table1
where column1 IN (@items)

possible duplicate of [Parameterizing a SQL IN clause?](http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause) (Although that doesn't specifically address reporting services) — Martin Smith, Nov 04 '11 at 13:15

sll · Accepted Answer · 2011-11-04T13:32:29.937

5

No, you can not. You can use table variable in following way:

DECLARE @table TABLE(item varchar(50))

IF @parameter = 3
BEGIN 
   INSERT INTO @table(item) VALUES ('third'), ('fourth')
END
ELSE
 BEGIN
   INSERT INTO @table VALUES(
     CASE @parameter      
        WHEN 1 then 'first'
       WHEN 2 then 'second'
     END 
    )
 END

-- And use IN(subquery)
SELECT column1     
FROM table1     
WHERE column1 IN (SELECT item FROM @table)

Also you can use EXISTS which much faster in such condition checks but you won't get significant performance improvements due to a small items cound (1-2)

SELECT column1     
FROM table1 t
WHERE EXISTS (SELECT * FROM @table WHERE item = t.column1)

edited Nov 04 '11 at 13:32

answered Nov 04 '11 at 13:16

sll

61,540
22
104
156

Because this type of selection is a recurring situation at my workplace, I'm going to go one further and set up a permanent table for these value associations and use a sub-query in the IN clause like you showed. Thanks for the inspiration. – hqrsie Nov 04 '11 at 13:25
@hqrsie - You can also pass table valued parameters to the stored procedure. Not sure how straight forward (or not) that is from reporting services though. – Martin Smith Nov 04 '11 at 13:27
@hqrsie : I've just updated my answer due to error in the first version (returned 'third, fourth' as a single varchar item) – sll Nov 04 '11 at 13:33
I'm finding the INSERT INTO line gives a syntax error on SQL 2005. Does this syntax require a later version of SQL? – NickG Oct 25 '13 at 10:41
Seems that syntax requires SQL 2008 or later. For < 2005 you'll need to use multiple INSERT statements I think. – NickG Oct 25 '13 at 10:44

score 0 · Answer 2 · answered Nov 04 '11 at 13:17

0

you could go with dynamic sql (not tested)

declare @items as varchar(50)
set @items =
  CASE @parameter
    WHEN 1 then '''first'''
    WHEN 2 then '''second'''
    WHEN 3 then '''third'', ''fourth'''
  END

declare @query = 'select column1 from table1 where column1 IN ('+@items+')'
exec @query

triple quotes are due to string concatenation mechanism

answered Nov 04 '11 at 13:17

Alex

23,004
4
39
73

Have you tried this? I believe this should not work because you've specified single variable of type varchar(50) in the IN() clause: `IN(@items)` – sll Nov 04 '11 at 13:35
TBH, I've been able to make it work with Dynamic-SQL, but was trying to find a way to do it without, as per the first line of my question. – hqrsie Nov 04 '11 at 13:47

Setting a variable to a list of strings for IN statement

2 Answers2