Geek asks and answers

The scope of the table variable inside the SQL cursor

If I run 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 expected to see only

someValue
ID5_ASDF

It seems that the @tempTable table variable is stored in the area between cursor iterations, but how then can you re-declare a variable at each iteration? It makes no sense to me.

I decided it

delete @tempTable

in each iteration, which also confirms my assumption that it is still in scope.

Can anyone explain this behavior?

+5
source share
2 answers

Yes, this is so - the area is not defined by the begin/ operator end, but by the end of the stored procedure orgo

- Transact-SQL, . , .

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

+5

T-SQL - beast - .

:

set @a = 2

"":

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

.

+2

More articles:


All Articles