Stored procs and 'in' predicate

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored procs and 'in' predicate

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-02-10 : 08:17:25

Dave writes "Maybe its my design thats wrong (wouldnt be the first time) but I have a requirement to pass a parameter into a sproc and use this as the basis for an in predicate in my select statement eg

sp_mysproc(@id int, @mylist varchar(1000)) as

SELECT myfield1, myfield2
FROM mytable
WHERE (mytable.IDfield = @id) and (myfield2 in "'" + @mylist + "'")

or something like that - bt i just cant get it to work, is this even possible? Please save whats left of my hair

Dave"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-10 : 08:23:03

you can't do it like that...

it either has to be dynamic sql or you need to employ one of these two techniques:

Where...
and ','+@myList+',' like '%,'+myField2+',%'

or

Where...
and myField2 in (dbo.split(@myList,','))
-- dbo.split is a udf that splits the string into a recordset

split function found here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45677

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-10 : 08:28:11

alter procedure sp_mysproc(@id numeric, @mylist varchar(1000)) as
Declare @sql varchar(200)
set @sql='SELECT myfield1, myfield2
FROM MyTable
WHERE (myfield1 = '+ convert(varchar(10),@id)+' ) and myfield2 in (''' + @mylist + ''')'
exec (@sql)

Madhivanan

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-10 : 08:31:51

oh and if you use the split function, you could actually do a join on the list instead of using the 'IN' keyword.

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources