Creating a SQL table from a comma concatenated list

I am running SQL Server and I have a stored procedure. I want do a select statement with a WHERE IN clause. I don't know how long the list will be so right now I have tried something as follows

SELECT * FROM table1 WHERE id IN (@idList)

in this solution @idList is a varChar(max). but this doesn't work. I heard about passing in table values, but I am co开发者_如何学运维nfused about how to do that. Any help would be great

I would suggest using a function to split the incoming list (use the link that Martin put in his comment).

Store the results of the split function in a temporary table or table variable and join it in your query instead of the WHERE clause

select * into #ids from dbo.Split(',', @idList)

select t.*
from table1 t
 join #ids i
    on t.id = i.s

The most efficient way would be to pass in a table valued parameter (if you're on SQL Server 2008), or an XML parameter (if you're on SQL Server 2005/2000). If your list is small (and you're on SQL Server 2005/2000), passing in your list as a comma (or otherwise) delimited list and using a split function to divide the values out into rows in a temporary table is also an option.

Whichever option you use, you would then join this table (either the table parameter, the table resulting from the XML select, or the temporary table created by the values from the split) to your main query.

Here is a table valued function that takes a nvarchar and returns a table to join on:

Create function [ReturnValues]
(
@Values nvarchar(4000)
)
Returns @ValueTable table(Value nvarchar(2000))
As
Begin
Declare @Start int
Declare @End int
Set @Start = 1
Set @End = 1

While @Start <= len(@Values)
Begin
      Set @End = charindex(',', @Values, @Start)
      If @End = 0
            Set @End = len(@Values) + 1
      Insert into @ValueTable
      Select rtrim(ltrim(substring(@Values, @Start, @End - @Start)))
      Set @Start = @End + 1
End
Return
End

GO

Binding an @idList parameter as you suggested is not possible with SQL.

The best would be bulk inserting the ids into a separated table and than query that table by using an subselect, or joining the IDs.

e.g.

INSERT INTO idTable (id, context) values (@idValue, 1);
INSERT INTO idTable (id, context) values (@idValue, 1);
INSERT INTO idTable (id, context) values (@idValue, 1); // as often as you like
SELECT * FROM table1, idTable WHERE table1.id == idTable.id and idTable.context = 1

The context must be a unique value that identifies the Id Range. That is important for running the stored proc parallel. Without the context information, running the stored procecure in parallel would mix the values from different selections.

If the number of parameters are reasonably small (< 100) you can use several parameters

SELECT * FROM table1 WHERE IN id IN (@id1, @id2, @id3)

If it is longer, look for a split function.