Passing a parameter for usin in a "Where xxx In " clause

Question

Post reply

Passing a parameter for usin in a "Where xxx In " clause

Dan Bradley-424087

Grasshopper

Points: 12
More actions
June 26, 2007 at 8:38 am

#117236

Hi
I want to be able to pass a parameter into a stored procedure which takes the form ('ABC', 'DEF', 'GHI') so that I can use the following and get a sensible answer out of it:
Select * from table1 where field1 in @param1
Where @param1 contains the string in brackets above. I must be slightly brain-dead but no matter what I try I either get an error or nothing.
Can anyone help please ?
Thanks
Dan

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

mrpolecat SSCertifiable Points: 6964 More actions · Answer 1

you can use dynamix sql

declare @sql varchar(8000)

set @sql = 'Select * from table1 where field1 in (' + @param1 + ')'

exec (@sql)

when you pass your parameter string you will need to escape your single quotes in the list.

Dan Bradley-424087 Grasshopper Points: 12 More actions · Answer 2

Thanks mrpolecat I'll give it a go. I think I'll run into other problems, because I want to use the select statement as part of a cursor (I've heard I shouldn't be using these, but they're very handy :-))

mrpolecat SSCertifiable Points: 6964 More actions · Answer 3

If you run into an issue post the rest of your code. I believe that, like all things, cursors in moderation are ok.

Lowell SSC Guru Points: 323482 More actions · Answer 4

also search for the Split() function in the scripts...there are several, and some return a table with multiple columns (ELEMENT,ELEMENTID), some just return a single column, etc. the split a string on a specified delimiter, like a comma.

you can pass the values to the split function and do something like this:

elect * from table1 where field1 in (select ELEMENT from dbo.Split(@param1,,',') )

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!