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.cc>