sql query problem
-
Hello
I have a stored proc which has a query as
select * from ChanTable where header1 IN (@parm1)
i am passing the parameter @parm1 to the stored proc as a varchar which contains values as a string
example '3a,3b'(that is I am concatinating string values '3a' & '3b' in 1 string and passing that to @parm1
so the actual select string is like
select * from ChanTable where header1 IN ('3a,3b')
but this does not give me the desired result as '3a,3b' is considered as 1 string I guess
But however if I give the sql select as
select * from ChanTable where header1 IN ('3a','3b') OR
select * from ChanTable where header1 IN (3a,3b)
the above 2 options give me the desired resultset. So how do I modify my sql proc to change the query to either 1 of the formats that work
Thanks a lot.
-
This is a FAQ that you've asked. You might want to read the articles by SQL Server MVP Erland Sommarskog on his homepage at http://www.sommarskog.se . Especially the one on arrays and the one on dynamic sql. And, yes, I know, these are long articles.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
You can try this solution. But this is only OK for little tables, as it allways results in a table scan. If the column au_lname contains ',' , you have the risk the select will return to much rows ...
select * from pubs.dbo.authors
where au_lname in ( 'White','Carson','Dull' )
-- with @param1, you could do this like :
declare @param1 varchar(255)
set @param1 = 'White,Carson,Dull'
select * from pubs.dbo.authors
where ','+@param1+',' like '%,' + au_lname + ',%'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply