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:
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 followDECLARE...BEGIN...END
format.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.