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

[Joel Thornton]

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?

Joel Thornton ~ <gr****@joelpt.eml.cc>

[oj]

Joel,

Erland has a decent writing on this topic.

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

--
-oj
http://www.rac4sql.net
"Joel Thornton" <jo****@eml.cc> wrote in message
news:c1**************************@posting.google.c om...

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?

Joel Thornton ~ <gr****@joelpt.eml.cc>