December 3, 2014 at 5:34 am
i have the following stored procedure to test scope of variables
alter proc updatePrereq
@pcntr int,
@pmax int
as
begin
if @pcntr = 1
begin
declare @i int
set @i= @pcntr
end
set @i=@pcntr
select @i;
end
go
In the above script @i is declare in the if block only when the @pcntr value is 1. Assume the above stored procedure is called 5 times from this script
declare @z int
set @z = 1
declare @max-2 int
set @max-2 = 5
while @z <= @max-2
begin
exec dbo.updatePrereq @z, @max-2
set @z = @z + 1
end
go
As i said earlier `@i` variable exists only when `@pcntr` is `1`. Therefore when i call the stored procedure for the second time and so forth the control cannot enter the if block therefore @i variable wouldn't even exist. But the script prints the value in `@i` in each iteration, How comes this is possible should it throw an error saying `@i` variable does not exist when `@pcntr` values is greater than `1`?
thanks
December 3, 2014 at 6:16 am
SQL doesn't have block scope for variables.
From Books Online (DECLARE)
The scope of a local variable is the batch in which it is declared
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply