存储过程中的 SQL Server 变量范围

发布于 2024-11-07 06:22:20 字数 369 浏览 0 评论 0原文

我想在 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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

旧街凉风 2024-11-14 06:22:20

来自在线图书：

变量的范围是可以引用该变量的 Transact-SQL 语句的范围。变量的作用域从声明它的那一刻起一直持续到声明它的批处理或存储过程结束为止。

然而。没有什么可以阻止你这样做：

create procedure Foo as begin

declare @bob int

if exists (x)
begin
    set @bob = 1
end
else
begin
    set @bob = 2
end

end

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.

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

回复收藏 0 原文

剧终人散尽 2024-11-14 06:22:20

不，SQL 非常有趣/奇怪，就像

在语句之前声明变量 如果存在 代码块

所以

declare @bob int 
set @bob = 2 

if exists(x) 
begin   
    set @bob = 1 
end

现在，看看这些示例并尝试猜测会发生什么

WHILE 1 = 2 --not true of course
BEGIN
  DECLARE @VAR INT;
END
SET @VAR = 1;

SELECT @VAR;

这当然有效，但并不是每次都初始化

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

No, SQL is pretty funny/weird like that

Declare the variable before the if exists block of code

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

回复收藏 0 原文

永不分离 2024-11-14 06:22:20

有什么原因你不能这样做：

declare @bob int 
if exists(x) 
begin   set @bob = 1 end 
else 
begin  set @bob = 2 end

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

回复收藏 0 原文

没︽人懂的悲伤 2024-11-14 06:22:20

您可以求助于使用动态 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

You could resort to using dynamic 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

回复收藏 0 原文

~没有更多了~