Table Variable and Database Scope

Question

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.

Answer 1

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

• Joann.B: Thank you Mark. This example comes from the training kit of "Exam 70-461: Querying Microsoft SQL Server 2012". The examples are trying to show me the visibilities of table variables in different levels.
• Mark Sinkinson: @Joann.B Ah ok, so maybe they were showing you examples of what should not work?
• Joann.B: So can I say that the difference between those 2 examples is implicitly or explicitly showing a batch end?
• Joann.B: To your above question: yes it is :)
• Joann.B: And one more question: can you give me some other examples that predicting a start of another batch, just like "exec"?