SQL Server procedure declare a list

You could declare a variable as a temporary table like this:

declare @myList table (Id int)

Which means you can use the insert statement to populate it with values:

insert into @myList values (1), (2), (5), (7), (10)

Then your select statement can use either the in statement:

select * from DBTablewhere id in (select Id from @myList)

Or you could join to the temporary table like this:

select *from DBTable djoin @myList t on t.Id = d.Id

And if you do something like this a lot then you could consider defining a user-defined table type so you could then declare your variable like this:

declare @myList dbo.MyTableType

That is not possible with a normal query since the in clause needs separate values and not a single value containing a comma separated list. One solution would be a dynamic query

declare @myList varchar(100)set @myList = '(1,2,5,7,10)'exec('select * from DBTable where id IN ' + @myList)

You can convert the list of passed values into a table valued parameter and then select against this list

DECLARE @list NVARCHAR(MAX)SET @list = '1,2,5,7,10';DECLARE @pos INTDECLARE @nextpos INTDECLARE @valuelen INTDECLARE @tbl TABLE (number int NOT NULL)SELECT @pos = 0, @nextpos = 1;WHILE @nextpos > 0BEGIN    SELECT @nextpos = charindex(',', @list, @pos + 1)    SELECT @valuelen = CASE WHEN @nextpos > 0                            THEN @nextpos                            ELSE len(@list) + 1                        END - @pos - 1    INSERT @tbl (number)        VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))    SELECT @pos = @nextpos;ENDSELECT * FROM DBTable WHERE id IN (SELECT number FROM @tbl);

In this example the string passed in '1,2,5,7,10' is split by the commas and each value is added as a new row within the @tbl table variable. This can then be selected against using standard SQL.

If you intend to reuse this functionality you could go further and convert this into a function.