SQL Server variable scope in a stored procedure

Question

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

RThomas · Accepted Answer · 2016-08-16 03:41:49Z

32

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

edited Aug 16, 2016 at 3:41

answered May 13, 2011 at 16:34

RThomas

10.9k2 gold badges50 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SQLMenace · Accepted Answer · 2011-05-13 16:33:14Z

9

No, SQL is pretty funny/weird like that

Declare the variable before the if exists block of code

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

answered May 13, 2011 at 16:33

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

1 Comment

Steve Boniface Over a year ago

I'm scratching my head over your WHILE 1 = 2 example... how???

HLGEM · Accepted Answer · 2011-05-13 16:32:03Z

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

answered May 13, 2011 at 16:32

HLGEM

97k15 gold badges120 silver badges191 bronze badges

4 Comments

Yuck Over a year ago

Exactly what I was going to say. Sounds like OP is only trying to do this for the sake of formatting.

Cade Roux Over a year ago

I think it's a bad particular example on the OP's point, there are certainly cases where a variable isn't leaving the scope of BEGIN/END and you may want to re-use a variable name within separate and restrictive scopes, and unfortunately this simply isn't available.

gh9 Over a year ago

That is what i am currently doing. There isn't a technical reason why I want to do it the way I mentioned. I was wondering if it was possible.

gh9 Over a year ago

@Yuck. Not just for formatting. I was working with some .net code and was curious if there was a way to scope variables in sql. Found out I didn't know if there is a possible way of doing it. I assumed there wasn't a way but i didnt know for fact or why you cant.

Lukas Eder · Accepted Answer · 2018-01-18 11:25:16Z

2

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

answered Jan 18, 2018 at 11:25

Lukas Eder

223k138 gold badges729 silver badges1.6k bronze badges

Collectives™ on Stack Overflow

SQL Server variable scope in a stored procedure

4 Answers 4

Comments

1 Comment

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related