Sql Variable To Use For Sql In Operator

[powpow]

Hey Everybody,

 

The issue I am having is that I would like to declare a varchar to hold a couple comma separated values to use in an in operator.

 

DECLARE @TEST varchar(1000);
SET @TEST='
''test1'',
''test2''
';

 

to confirm the value is set correctly I run a select on the variable

 

Select @TEST;

 

The result is 'test1','test2' which is what I am looking to place in my where clause:

 

Select *
from testTable
where col in (@TEST);

 

the result should return rows but nothing is returned. I also tried to use where col =any(@TEST) and it errors.

 

If any one can see the error in my ways I would be grateful. Thank you.

[kicken]

You can't do what you're trying to do.  The closest you could get to using a variable with multiple items in the IN clause is to use a table value varaible, such as:

DECLARE @test TABLE (
   s VARCHAR(100)
)

INSERT INTO @test VALUES ('test1'),('test2');

SELECT * FROM testtable
WHERE col1 IN (SELECT s FROM @test)

 

[powpow]

Thank you Kicken,

 

I code in php and was expecting variables to work similarly. Thank you for giving me a work around, I build a report which consists of 10 unions where the criteria corresponds to the example I gave. In the past, I have replaced the criteria by using the replace function and believe that is what I plan on using moving forward.