存储过程中的SQL Server变量作用域

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

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

No, SQL is pretty funny/weird like that

不，SQL很有趣/很奇怪

Declare the variable before the if exists block of code

在if存在代码块之前声明变量

所以

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

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

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

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