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
5 个解决方案
#1
19
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
#2
6
No, SQL is pretty funny/weird like that
不,SQL很有趣/很奇怪
Declare the variable before the if exists
block of code
在if存在代码块之前声明变量
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
#3
5
No.
不。
Microsoft has said they won't fix this:
微软表示不会解决这个问题:
https://connect.microsoft.com/SQLServer/feedback/details/537275/make-it-possible-to-declare-variables-that-are-only-visible-within-a-block
#4
3
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
#5
1
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
#1
19
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
#2
6
No, SQL is pretty funny/weird like that
不,SQL很有趣/很奇怪
Declare the variable before the if exists
block of code
在if存在代码块之前声明变量
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
#3
5
No.
不。
Microsoft has said they won't fix this:
微软表示不会解决这个问题:
https://connect.microsoft.com/SQLServer/feedback/details/537275/make-it-possible-to-declare-variables-that-are-only-visible-within-a-block
#4
3
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
#5
1
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