存储过程中的 SQL Server 变量作用域
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5994957/
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
SQL Server variable scope in a stored procedure
提问by gh9
I would like to declare a variable within an if/else statement in a SQL Server stored procedure. I understand that this is fairly impossible because SQL Server doesn't do memory management with respect to declaration of variables within procedures. Is there a way to have a variable scoped in an if/else statement, then redeclare a variable with the same name in another if/else statement? For example:
我想在 SQL Server 存储过程的 if/else 语句中声明一个变量。我知道这是相当不可能的，因为 SQL Server 不对过程中的变量声明进行内存管理。有没有办法在 if/else 语句中设置一个变量范围，然后在另一个 if/else 语句中重新声明一个同名的变量？例如：
create procedure Foo
as
begin
if exists (x)
begin
declare @bob int
set bob = 1
end
else
begin
declare @bob int
set bob = 2
end
end
回答by RThomas
From books online:
从网上书籍：
The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared.
变量的范围是可以引用该变量的 Transact-SQL 语句的范围。变量的作用域从它被声明的那一刻开始，直到它被声明的批处理或存储过程结束。
However. Nothing keeps you from doing this:
然而。没有什么能阻止你这样做：
create procedure Foo as begin
declare @bob int
if exists (x)
begin
set @bob = 1
end
else
begin
set @bob = 2
end
end
回答by SQLMenace
No, SQL is pretty funny/weird like that
不，SQL 很有趣/很奇怪
Declare the variable before theif existsblock of code
在代码块之前声明变量if exists
so
所以
declare @bob int
set @bob = 2
if exists(x)
begin
set @bob = 1
end
Now, take a look at these examples and try to guess what happens
现在，看看这些例子，试着猜猜会发生什么
WHILE 1 = 2 --not true of course
BEGIN
DECLARE @VAR INT;
END
SET @VAR = 1;
SELECT @VAR;
This of course works, but it is not initialized every time
这当然有效，但不是每次都初始化
DECLARE @loop INT
SET @loop = 0
WHILE @loop <=6
BEGIN
DECLARE @VAR INT
SET @VAR = COALESCE(@VAR,0) + 1
SET @loop = @loop +1
END
SELECT @VAR
回答by HLGEM
is there some reason why you can't do :
有什么原因你不能这样做：
declare @bob int
if exists(x)
begin set @bob = 1 end
else
begin set @bob = 2 end
回答by Lukas Eder
You could resort to using dynamic SQL:
您可以求助于使用动态 SQL：
if exists (x)
begin
exec sp_executesql N'
declare @bob int
set @bob = 1
';
end
else
begin
exec sp_executesql N'
declare @bob int
set @bob = 2
';
end
