Link to home
Start Free TrialLog in
Avatar of thayduck
thayduckFlag for United States of America

asked on

SQL IN Statement

When passing multiple selections from a parm to a sql stored procedure,  I usually build a temp table to split those parms out to individual records then  read that table in a IN statement (in my Where clause) in my stored procedure.

Now, if I do below:

 DECLARE @pcategoryo VARCHAR(max)      
     
     SET @pcategoryo = '''' + Replace(@pcategory, ',', ''', ''') + ''''

@pcategoryo will contain   'CATS', 'DOGS', 'LICE'

Now when I do a :

 WHERE  catname IN(@pcategoryo)  I get no records, when I should be.

What do I have to do to get this to work or should I stick to the table thing ?
ASKER CERTIFIED SOLUTION
Avatar of Cluskitt
Cluskitt
Flag of Portugal image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of thayduck

ASKER

Did:

SET @pcategoryo = '|' + Replace(@pcategory, ',', ''', ''') + '|'

and tried both :

WHERE @pcategoryo LIKE '%|'+team+'|%'

WHERE CHARINDEX('|'+team+'|',@pcategoryo)>0


and did not get records.



* actual field name is  team
Sorry, I do get records if mult select parm has only 1 choice. If more than 1 choice, i get no records.
What is the value of @pcategoryo? You need it to be something like:
|CATS|DOGS|LICE|

That probably means changing the way you build your variable. If your variable comes, as it seems, in the format:
'CATS','DOGS','LICE'

Then you need to use:
SET @pcategoryo=REPLACE(REPLACE(@pcategoryo,''',''','|'),',','|')

Or just change the way the variable is fed into the procedure in the first place.
This worked:

SET @pcategoryo = '|' + Replace(@pcategory, ',', '|') + '|'      ( =   |CATS|DOGS|LICE|)



WHERE @pcategoryo LIKE '%|'+team+'|%'
Thanks for your QUICK help.
Glad to help.