Declare a variable list in SQL Server stored procedure

Question

I'd like to delete data from multiple tables with the same conditions (where clause) for each delete statement.

delete from tblA where id in (select x.id from tblX x where name like N'%test%')
delete from tblB where id in (select x.id from tblX x where name like N'%test%')
delete from tblC where id in (select x.id from tblX x where name like N'%test%')
delete from tblD where id in (select x.id from tblX x where name like N'%test%')

Is there a way to declare a list that stores the ids from the select statement above?

I tried:

declare @ids int
set @ids = select x.id from tblX x where name like N'%test%'

But it complains that

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Please advise, thanks.

Think about why top 1 makes this work. http://stackoverflow.com/questions/11232751/sql-error-subquery-returned-more-than-1-value — Tim, Feb 22 '13 at 19:00

lolol · Accepted Answer · 2018-03-01T15:38:31.140

You will need a table anyways, but at least you avoid tons of processing by doing a like everytime:

-- create a table variable
declare @ids table
(
  id int not null
)

-- insert the id into the table variable
insert into @ids
select id from table1 where column1 like '%something%'

-- delete
delete from tablen where id in (select * from @ids)

You can also use a temporary table, it looks like the same, but instead of @ids, you will need #ids, and you need to drop the temporary table after the job is done.

To choose between a temporary table (physical table) or table variable (memory like table) you will really need to do some tests, but by definition complex data works better in temporary tables. If you just need to hold a small numbers of ids for a short period I'm very sure that table variables are better.

What's the difference between a temp table and table variable in SQL Server?

Table variables are not memory only. That is a myth. For the non mythical differences see [my answer here](http://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386) — Martin Smith, Feb 23 '13 at 13:48
I didnt really meant that, and also posted a link to the differences. — lolol, Feb 24 '13 at 23:19

score 2 · Answer 2 · answered Feb 22 '13 at 19:00

You could declare a temporary table and then select into that table the ids that you will be deleting.

CREATE TABLE #IDS_from_tblA (id int)
INSERT INTO #IDS_from_tblA(id)
    SELECT x.id FROM tblA WHERE x.id in (select x.id from tblX x where name like N'%test%')
delete from tblA where id in (select x.id from tblX x where name like N'%test%')

(Do whatever you want to do with the ids)

DROP TABLE #IDS_from_tblA

score 2 · Answer 3 · answered Feb 22 '13 at 19:01

In sql server 2008+

declare @IdList table (Id int primary key)

insert into @IDList (Id)
select x.id from tblX x where name like N'%test%'

delete from tblA where id in (select x.id from @IDList x)

If you have more than some hundreeds of records, you can use temp table instead of table variable.

Declare a variable list in SQL Server stored procedure

3 Answers3