I've come across a interesting scenario (at least for me) in a stored procedure. Would like to have experts opinion and thoughts on it.
DECLARE @loopcounter INT
SET @loopcounter=10
WHILE @loopcounter > 0
BEGIN
DECLARE @insidevalue int
IF (@loopcounter%2 = 0)
SET @insidevalue = @loopcounter
PRINT 'Value_' + CAST(@insidevalue AS NVARCHAR) + '_'
SET @loopcounter = @loopcounter - 1
END
I was expecting this block will give the output as below
Value_10_
Value_ _
Value_8_
Value_ _
Value_6_
Value_ _
Value_4_
Value_ _
Value_2_
Value_ _
Instead I got output as below:
Value_10_
Value_10_
Value_8_
Value_8_
Value_6_
Value_6_
Value_4_
Value_4_
Value_2_
Value_2_
I thought if I declare a variable inside a while block, then for every iteration it will reset the value to NULL or default value (from c# background).
If this is by design then my question is how does SQLServer treat 'DECLARE' statement for that variable inside while block? Does it ignore it as the variable is already in memory?
Can somebody please explain me this behavior?
The variable scope is the whole batch in this case a stored procedure.
It isn't re-declared every loop
So this is exactly as expected
Edit:
There is a recent blog article which is quite similar. The author was quickly corrected :-)
From Transact-SQL Variables
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.
The DECLARE
is not in itself an executable statement. The variable declarations are all identified at compile time and the memory reserved for them in the execution context.
If you use the 2008+ Declare and Set syntax. The Set part of the statement will occur every loop iteration however.
DECLARE @loopcounter INT
SET @loopcounter=10
WHILE @loopcounter > 0
BEGIN
DECLARE @insidevalue INT = NULL
IF (@loopcounter%2 = 0)
SET @insidevalue = @loopcounter
PRINT 'Value_' + CAST(@insidevalue AS NVARCHAR) + '_'
SET @loopcounter = @loopcounter - 1
END
Try this for fun
if 1 = 0
begin
-- will never happen
declare @xx int
end
else
begin
set @xx = 1
end
print @xx
Apparently the declare code does not have to be executed. Only be declared before it is used.
This don't work
if 1 = 0
begin
-- will never happen
set @xx = 1
end
else
begin
declare @xx int
end
print @xx
From Declare:
The scope of a local variable is the batch in which it is declared.
There are no more "local" scoping rules in T-SQL. It also means that you can't declare the same variable name inside IF and ELSE blocks.
All Declare does is declare a variable. It has no relation to assignment. The value of any variable that has never been assigned to is NULL
. But thereafter, the only way the variables value will become NULL
again is through an explicit assignment.
If you need it to be NULL
at the top of each loop iteration, therefore, you must explicitly assign it.
In T-SQL a WHILE..END
is not individually scoped, you can for example SELECT @insidevalue
after the WHILE
's END
.
DECLARE @loopcounter INT
SET @loopcounter=10
WHILE @loopcounter > 0
BEGIN
DECLARE @insidevalue int
IF (@loopcounter%2 = 0)
begin
set @insidevalue=@loopcounter
PRINT 'Value_' + CAST(@insidevalue AS NVARCHAR) + '_'
end
ELSE
PRINT 'Value_' + ' ' + '_'
SET @loopcounter = @loopcounter - 1
END
DECLARE @loopcounter INT
DECLARE @insidevalue int
SET @loopcounter=10
WHILE @loopcounter > 0
BEGIN
IF (@loopcounter%2 = 0)
BEGIN
SET @insidevalue = @loopcounter
PRINT 'Value_' + CAST(@insidevalue AS NVARCHAR) + '_'
END
ELSE
PRINT 'Value_'+' '+'_'
SET @loopcounter = @loopcounter - 1
END