SQL 使用 IF 语句检查临时表在存储过程中是否有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25151646/
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
Using IF statement to check if temporary table has rows in a stored procedure
提问by marchemike
How can I use an IF statement in my stored procedure to check if my temporary table has rows?
如何在我的存储过程中使用 IF 语句来检查我的临时表是否有行？
I want to check if #TempDataTable has rows then I would do either of the two statements, insert or update depending on the output.
我想检查 #TempDataTable 是否有行，然后我会根据输出执行两个语句中的任何一个，插入或更新。
I did the following:
我做了以下事情：
BEGIN
SELECT *
INTO #TempDataTable
FROM
(SELECT *
FROM BranchNameTable BT
LEFT JOIN BranchLocationActivate BLA ON BT.loc_id = BLA.loc_id
WHERE BT.Branchloc = 1;) as Q
if(//TempDataTable has 0 rows)
INSERT INTO BranchLocationActivate
VALUES(//my values)
ELSE
UPDATE BranchLocationActivate
SET //My values where //my values
END
How do I put it after my select statement in my procedure??
我如何将它放在我的程序中的 select 语句之后？
回答by Gordon Linoff
You could use not exists:
你可以使用not exists：
if (not exists (select 1 from #tempdataatable))
回答by Solomon Rutzky
Assuming that there are not any SQL statements between SELECT * INTO #TempDataTableand if(//TempDataTable has 0 rows)then you don't even need the temp table in the first place. Instead you should simplify the test to be just:
假设两者之间没有任何 SQL 语句SELECT * INTO #TempDataTable，if(//TempDataTable has 0 rows)那么您甚至根本不需要临时表。相反，您应该将测试简化为：
IF (NOT EXISTS(
SELECT *
FROM BranchNameTable BT
LEFT JOIN BranchLocationActivate BLA
ON BT.loc_id = BLA.loc_id
WHERE BT.Branchloc = 1
)
)
BEGIN
INSERT INTO BranchLocationActivate VALUES(//my values)
END;
ELSE
BEGIN
UPDATE BranchLocationActivate SET //My values where //my values
END;
If there are statements between those parts that make use of the temp table, then you can simplify by using the information SQL Server already gives you after the DML statement via the @@ROWCOUNTvariable:
如果这些部分之间有使用临时表的语句，那么您可以使用 SQL Server 在 DML 语句之后通过@@ROWCOUNT变量提供的信息来简化：
DECLARE @RowsInserted INT;
SELECT *
INTO #TempDataTable
FROM BranchNameTable BT
LEFT JOIN BranchLocationActivate BLA
ON BT.loc_id = BLA.loc_id
WHERE BT.Branchloc = 1;
SET @RowsInserted = @@ROWCOUNT;
-- other statements
IF (@RowsInserted = 0)
BEGIN
INSERT INTO BranchLocationActivate VALUES(//my values)
END;
ELSE
BEGIN
UPDATE BranchLocationActivate SET //My values where //my values
END;
回答by Nadeem_MK
You can either proceed with the solution mentioned by Gordon Linoff above, or if the the number of rows might help / a simpler way would be the below;
您可以继续使用上面 Gordon Linoff 提到的解决方案，或者如果行数可能有帮助/更简单的方法如下；
DECLARE @Count AS INT
Select @Count = count (*) from #tempdataatable
If @Count = 0
BEGIN
...
ELSE
...
END
