Passing an IN (a, b, c) list to a sproc as a string -- best method? -- sql server (alternate)

joelpt NO[at]SPAM eml.cc
1/7/2004 8:12:24 PM

I want to do something like this in a stored proc:

------

Create Procedure dbo.GetPatients
@PatientIdList varchar(200) -- comma separated list of PatientIDs
As

Select *
From Patients
Where PatientId In (@PatientIdList)

------

I know the above won't work, but of course what I want is if
@PatientIdList = '1,2,3' then I want Patient records with PatientIds
1, 2, and 3 returned.

It looks like the only way to do this is to build the SQL statement as
a string within the stored procedure ... which pretty much defeats the
usefulness of using precompiled sprocs as I understand it (better off
building a dynamic query against a View in that case).

Thoughts?

oj
1/8/2004 6:05:52 AM

Joel,

Erland has a decent writing on this topic.

http://www.sommarskog.se/arrays-in-sql.html

--
-oj
http://www.rac4sql.net

[quoted text, click to view]

sql server (alternate) : Passing an IN (a, b, c) list to a sproc as a string -- best method?