gh9 gh9 - 3 months ago 6
SQL Question

SQL Server variable scope in a stored procedure

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

Answer

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