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

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 ~ <groups@.joelpt.eml.cc>Joel,

Erland has a decent writing on this topic.

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

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

"Joel Thornton" <joelpt@.eml.cc> wrote in message
news:c190a45a.0401072012.5c38ba06@.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 ~ <groups@.joelpt.eml.ccsql