Alex Doro Alex Doro - 11 months ago 65
SQL Question

SQL Server procedure declare a list

My SQL code is fairly simple. I'm trying to select some data from a database like this:

WHERE id IN (1,2,5,7,10)

I want to know how to declare the list before the select (in a variable, list, array, or something) and inside the select only use the variable name, something like this:

VAR myList = "(1,2,5,7,10)"
WHERE id IN myList

Answer Source

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 DBTable
where id in (select Id from @myList)

Or you could join to the temporary table like this:

select *
from DBTable d
join @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