3

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.

5

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
  • 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. – Joann.B Jun 27 '14 at 18:36
  • @Joann.B Ah ok, so maybe they were showing you examples of what should not work? – Mark Sinkinson Jun 27 '14 at 18:38
  • So can I say that the difference between those 2 examples is implicitly or explicitly showing a batch end? – Joann.B Jun 27 '14 at 18:38
  • To your above question: yes it is :) – Joann.B Jun 27 '14 at 18:39
  • And one more question: can you give me some other examples that predicting a start of another batch, just like "exec"? – Joann.B Jun 27 '14 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.