SQL Server procedure declare a list

Question

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

SELECT * FROM DBTable 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)" SELECT * FROM DBTable WHERE id IN myList 

Answer 1

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 

Answer 2

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 + ')')