Creating a SQL table from a comma-separated list

Question

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

SELECT * FROM table1 WHERE id IN (@idList)

in this solution, @idList is varChar (max). but that will not work. I heard about passing table values, but I'm confused about how to do this. Any help would be great

Answer 1

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

split WHERE

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

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

Answer 2

( SQL Server 2008) XML ( SQL Server 2005/2000). ( SQL Server 2005/2000), ( ) split .

, ( , , XML, , ) .

Answer 3

, nvarchar :

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

Answer 4

@idList, , SQL.

, .

.

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

, . proc . .

Answer 5

(< 100),

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

, .