在 SQL Server 存储过程中声明一个变量列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15031247/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Declare a variable list in SQL Server stored procedure
提问by Eddie
I'd like to delete data from multiple tables with the same conditions (where clause) for each delete statement.
对于每个删除语句，我想从具有相同条件（where 子句）的多个表中删除数据。
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?
有没有办法声明一个列表来存储上面select语句中的id？
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.
子查询返回了 1 个以上的值。当子查询跟随 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的。
Please advise, thanks.
请指教，谢谢。
回答by lolol
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.
你也可以使用临时表，看起来是一样的，但是你需要#ids而不是@ids，而且你需要在工作完成后删除临时表。
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.
要在临时表（物理表）或表变量（如表的内存）之间进行选择，您确实需要进行一些测试，但根据定义，复杂数据在临时表中效果更好。如果您只需要在短时间内持有少量 id，我非常确定表变量会更好。
What's the difference between a temp table and table variable in SQL Server?
回答by Narnian
You could declare a temporary table and then select into that table the ids that you will be deleting.
您可以声明一个临时表，然后将要删除的 ID 选择到该表中。
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
回答by Morzel
In sql server 2008+
在 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.
如果您有超过数百条记录，则可以使用临时表而不是表变量。
