Jump to content


Photo

Sql Variable To Use For Sql In Operator

sql

  • Please log in to reply
2 replies to this topic

#1 powpow

powpow

    Advanced Member

  • Members
  • PipPipPip
  • 59 posts

Posted 14 November 2012 - 11:42 AM

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.

#2 kicken

kicken

    Wiser? Not exactly.

  • Gurus
  • 2,707 posts
  • LocationBonita, FL

Posted 14 November 2012 - 04: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:
DECLARE @test TABLE (
   s VARCHAR(100)
)

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

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

Recycle your old CD's, don't trash them!
Did I help you out?  Feeling generous? I accept tips via Paypal or Bitcoin @ 14mDxaob8Jgdg52scDbvf3uaeR61tB2yC7

#3 powpow

powpow

    Advanced Member

  • Members
  • PipPipPip
  • 59 posts

Posted 15 November 2012 - 10:51 AM

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

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com