Store a list of numbers returned by a query into a variable

I’d like to store the Ids returned by a query like so into a variable:

SELECT FooId FROM Bar; -- FooId is of type int

So that I may later say something like:

DELETE FROM Foo WHERE Id IN @TheFooIdsIGotFromThePreviousQueryAbove;

How do I do that? Specifically, what datatype must I declare such a list variable to be of?

Notice that I could have simply done:

DELETE FROM Foo WHERE Id IN (SELECT FooId FROM Bar);

But I don’t can’t do that for reasons that will simply complicate the question.

I am using Microsoft SQL Server 2014.

Answer

use temp table or table variable to store the Id

-- declare table variable
declare @IDS table (Id int)

-- insert into the table variable
insert into @IDS (Id) SELECT FooId FROM Bar

-- join the table variable to the table you want to delete
DELETE d
FROM   Foo d
       INNER JOIN @IDS i ON d.Id = i.Id

Advertisement

Answer