Jump to content


Sql Variable To Use For Sql In Operator


  • Please log in to reply
2 replies to this topic

#1 powpow

  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts

Posted 14 November 2012 - 04:42 PM

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);

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.

#2 kicken

  • Gurus
  • Wiser? Not exactly.
  • 3,313 posts
  • LocationBonita, FL

Posted 14 November 2012 - 09:24 PM

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:
   s VARCHAR(100)

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

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

Did I help you out? Feeling generous? I accept tips via Bitcoin @ 14mDxaob8Jgdg52scDbvf3uaeR61tB2yC7
Kicken's World⦄ ⦃Recycle old CD's

#3 powpow

  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts

Posted 15 November 2012 - 03:51 PM

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.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users