Scope of table variable within SQL cursor

Question

If I run the below in MS SQL 2008 R2 I get an unexpected result.

create table #DataTable (someID varchar(5))
insert into #DataTable 
values ('ID1'),('ID2'),('ID3'),('ID4'),('ID5')

declare @data varchar(8);

declare myCursor cursor for
select someID from #DataTable

open myCursor
FETCH NEXT FROM myCursor INTO
@data

WHILE(@@Fetch_Status >=0)
BEGIN 

    declare @tempTable table (someValue varchar(10))

    insert into @tempTable select @data + '_ASDF'
    select * from @tempTable    

FETCH NEXT FROM myCursor INTO
@data

END

close myCursor
deallocate myCursor

drop table #DataTable

Result of the last iteration:

someValue
ID1_ASDF
ID2_ASDF
ID3_ASDF
ID4_ASDF
ID5_ASDF

I haved expected only to see

someValue
ID5_ASDF

It seems that the table variable @tempTable is kept in scope between cursor iterations - but how is it then possible to re-declare the variable in each iteration ? Makes no sense to me.

I solved it by

delete @tempTable

in each iteration - which also backs up my assumption about it still being in scope.

Can anyone explain this behavior ?

Orvie

Answer 1

Yes, it does - the scope isn't defined by the begin / end statements, but by the end of a stored procedure, or a go

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.

http://msdn.microsoft.com/en-us/library/ms187953(v=sql.105).aspx

Pumice

thanks - but how is it then possible to declare the variable again in each iteration ? – Orvie

Answer 2

Variable declarations in T-SQL are a bit of an odd beast - variable declarations ignore control flow.

This produces an error:

set @a = 2

This runs without issue, and doesn't print "Never":

if 1=0
begin
    print 'Never'
    declare @a int
end
set @a = 2

The lifetime of a variable is from the point of declaration until the batch completes.

Olimpiaolin

ha, strange. I think there is no reason for me to wonder about how it is possible to declare the same variable twice in cursors when you show me something like that :) – Orvie

@Orvie - since declarations ignore control flow, they don't get declared more than once inside any loops or cursors. If they did, you would get an error and wouldn't be allowed to ever declare a variable inside of a loop. If you explicitly had two declare statements with the same variable name, you'd then get an error whether they are in a loop or not. – Admit