存储过程中的 SQL Server 变量作用域 [英] SQL Server variable scope in a stored procedure

问题描述

我想在 SQL Server 存储过程的 if/else 语句中声明一个变量.我知道这是相当不可能的，因为 SQL Server 不对过程中的变量声明进行内存管理.有没有办法在 if/else 语句中设置一个变量范围，然后在另一个 if/else 语句中重新声明一个同名的变量?例如:

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:

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

推荐答案

来自 书籍在线:

变量的范围是可以引用该变量的 Transact-SQL 语句的范围.变量的范围从它被声明的那一刻开始，直到它被声明的批处理或存储过程结束.

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.

不过.没有什么可以阻止您这样做:

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

这篇关于存储过程中的 SQL Server 变量作用域的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！