sql - 声明变量并调用

Question

我有一堆删除查询，我必须为每个查询复制值。例如;

delete from source where id in (3300,3301, 2872)
delete from performance where srcid in (3300,3301, 2872)
delete from title where id in (3300,3301, 2872)
delete from name where srcid in (3300,3301, 2872)

我想声明这些值 (3300,3301, 2872)，因为它们每次都会改变。我必须为每个查询复制。我了解当它是一个带有声明并设置 @source = 的数字时如何声明，但我对如何进入感到困惑

我尝试了很多来自网络的变体。这是最新的变化。它不会给出错误，但它不会在列表中找到项目。我无法从网上弄清楚 sp 或函数的创建。

DECLARE @sourcelist VARCHAR = '2380,2379'
--SET @sourcelist = '2380' + ',' + '2379'

select * from dbo.Source where id in (@sourcelist)



DECLARE @sourcelist VARCHAR = '2380,2379'
--SET @sourcelist = '2380' + ',' + '2379'

select * from dbo.Source where id in (@sourcelist)

--------------------------------

这是sp的变体

DECLARE @sourcelist VARCHAR(500)
DECLARE @delete1 NVARCHAR(4000)
SET @delete1 = 'delete from [EID].[FileLoadLog] where srcid in (CAST(@sourcelist AS varchar(500)))'

BEGIN
EXEC sp_executesql @delete1 
END

这些都是一排。我希望删除 2 行。

标签： sqlsql-servertsql

Answer 1

您也可以使用表变量来执行此操作

declare @items table(
  id int
);

insert @items(id)
values (3300),(3301), (2872);

delete from source where id in (select id from @items);
delete from performance where srcid in (select id from @items);
delete from title where id in (select id from @items);
delete from name where srcid in (select id from @items);