Declare a variable list in SQL Server stored procedure
sql, sql-server-2012
Solution
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?
Problem
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.