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 ?
The table variable scope is within the batch. We can define a table variable inside a stored procedure and function as well. In this case, the table variable scope is within the stored procedure and function. We cannot use it outside the scope of the batch, stored procedure or function.
Table variables can be declared within batches, functions, and stored procedures, and table variables automatically go out of scope when the declaration batch, function, or stored procedure goes out of scope. Within their scope, table variables can be used in SELECT, INSERT, UPDATE, and DELETE statements.
Definition. The table variable is a special type of the local variable that helps to store data temporarily, similar to the temp table in SQL Server. In fact, the table variable provides all the properties of the local variable, but the local variables have some limitations, unlike temp or regular tables.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With