Sql-server – Table Variable and Database Scope

sql serverstored-procedurestemporary-tables

I am a junior DBA, and I am still confused by database scope.

Now I am learning about local/global temp table and table variables. I saw an example as following:

declare @T1 as table 
(   
    col1 int not null
);

insert into @T1(col1) values (10);

exe('select col1 from @T1;');

go

When I run it in SQL Server, I got below message:

(1 row(s) affected)
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@T1".

The message means the insert statement was successful while the exec failed. I am confused because:

  1. I don't know why SQL Server considers the @T1 declaration and the insert statement as one block. My colleague told me to regard them as a stored procedure. But I thought a stored procedure should at least follow DECLARE...BEGIN...END format.

  2. There is another example right after this one in book:

    declare @T1 as table 
(   
     col1 int not null
);

insert into @T1(col1) values (10);
go

exec('select col1 from @T1;');
go

And it gave me same error. I don't know what the book is going to show me by using these 2 examples. Or in another word, I don't understand the use of "GO".

Can anyone help? Thanks so much.

Best Answer

Table variables have the scope of the batch or stored procedure they are run in. There is no need to drop the table variable at the end of a batch, however you will not be able to reference the table variable in consecutive batches.

If you execute the SQL as one block, without the exec, then your SELECT will be able to 'see' the table variable and select the data from it.

declare @T1 as table 
(   
    col1 int not null
);

insert into @T1(col1) values (10);

select col1 from @T1; -- returns 10

go

The exec encapsulates a T-SQL batch, so in your example it is in a completely separate batch from the table declaration and the insert statement and therefore the table variable is not in scope.

Exec does not terminate a batch though, the table variable will still be in scope after the exec is run.

declare @T1 as table 
(   
    col1 int not null
);

insert into @T1(col1) values (10);

exec('select col1 from @T1;'); -- errors

select col1 from @T1; -- returns 10

go

However, the GO command signals the end of a batch, so placing a GO between the insert and select will also mean you are executing 2 separate batches. You would then also get the error

declare @T1 as table 
(   
    col1 int not null
);

insert into @T1(col1) values (10);

exec('select col1 from @T1;'); -- errors

GO

select col1 from @T1; -- errors

GO
Related Question