SQL Server procedure declare a list

sql sql-server sql-server-2008-r2
Alex Doro picture Alex Doro · Jan 16, 2014 · Viewed 144.9k times · Source

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

Peter Monks picture Peter Monks · Jan 16, 2014

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

Related questions

Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build). Sometimes when I run my build I get this error: ALTER DATABASE failed because a lock could not be placed on …

Read More
Select SQL Server database size

how can i query my sql server to only get the size of database? I used this : use "MY_DB" exec sp_spaceused I got this : database_name database_size unallocated space My_DB 17899.13 MB 5309.39 MB It returns me several …

Read More
CREATE TABLE IF NOT EXISTS equivalent in SQL Server

CREATE TABLE IF NOT EXISTS works on mysql but fails with SQL Server 2008 R2. What is the equivalent syntax?

Read More