[RESOLVED] stored procedure multi-select question

[szlamany]

Go into QUERY ANALYZER and copy this into a query window...

Set NoCount On

Declare @ListStr varchar(100)

Set @ListStr='10,20,30,35,36,37'

Declare @ListTbl Table (SelectValue int)
Declare @CP int
Declare @SV int

While @ListStr<>''
Begin
	Set @CP=CharIndex(',',@ListStr)
	If @CP<>0
	Begin
		Set @SV=Cast(Left(@ListStr,@CP-1) as int)
		Set @ListStr=Right(@ListStr,Len(@ListStr)-@CP)
	End
	Else
	Begin
		Set @SV=Cast(@ListStr as int)
		Set @ListStr=''
	End
	Insert into @ListTbl Values (@SV)
End

Select * From @ListTbl

So what you see with this example is that you can take a STRING of comma-separated values and put them into a TABLE VARIABLE (or a TEMPORARY TABLE if desire).

Then you do your query like this:

Select * From SomeTable Where Id in (Select SelectValue From @ListTbl)

Or...

Select * From @ListTbl LT
Left Join SomeTable ST on ST.Id=LT.SelectValue

or I'm sure several other variations would work as well...