December 9, 2010 at 9:49 pm
Comments posted to this topic are about the item What is the result of the following query
December 9, 2010 at 10:03 pm
This is a very good back to basics question, thank you.
The behaviour of the exec in this case is identical to the one of the stored proc where the temp table created inside is descoped once the proc bails out. In other words, if create temp table part were moved out then the temp table would be visible to the exec batch and after, i.e.
if object_id('tempdb.dbo.#tmp') is not null drop table #tmp;
create table #tmp (id int);
declare @sql varchar(2000);
set @sql = 'insert into #tmp(id) values (1);';
exec (@sql);
select * from #tmp;
results in
id
-----------
1
Oleg
December 9, 2010 at 11:43 pm
Good question but the answers are a bit... unfair.
The 2 answers:
Throw an error: Invalid object name '#tmp'.
Throw an error: Cannot find table #tmp inside TempDB.
Have the same effect (no #tmp table). So you would also have to know the wording of the error SQL would throw in order to get it right... without running it.
/T
December 10, 2010 at 12:22 am
Very good, basic question. Clearly signifies the importance of scoping things right, which most tend to forget when piling on tons of code.
Thank-you!
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
December 10, 2010 at 1:15 am
Nice question, thanks.
(but it surprises me that apparently 78% -for the moment- know the exact error message!)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 10, 2010 at 1:20 am
da-zero (12/10/2010)
Nice question, thanks.(but it surprises me that apparently 78% -for the moment- know the exact error message!)
Could have done what i did... ran the code. Now i knew that it wouldnt find the #tmp table but didnt know the exact error message (like i care about the exact wording of an errormessage). Cheating... maybe maybe not... it depends 😛
Like i wrote a bit up... the second error message about not being able to find #tmp shouldnt have been there.
/T
December 10, 2010 at 2:09 am
da-zero (12/10/2010)
(but it surprises me that apparently 78% -for the moment- know the exact error message!)
Well, I don't know about others, but for me this is one of the more common errors I see when developing. Or when copying and pasting code snippets from internet support forums. I would not be able to reproduce it out of the blue, but I did recognise the "invalid object name" error, and I have not ever seen a message that even resembles the "cannot find ... inside TempDB" one. (plus, that one is extremely unlikely, as the database for temporary object is called tempdb, not TempDB - but I guess only people who are in the habit of developing on a case sensitive instance would know that).
December 10, 2010 at 3:20 am
I got it right but I had to think about this one.
I thought the error was going to be caused by not having a space between the two commands.
Set @strSql = @strSql + 'Create table #tmp (id int)'
Set @strSql = @strSql + 'Insert into #tmp(id) values (1)'
This should produce the same as (Notice no spacing between two commands):
Set @strSQL = 'Create table #tmp (id int)Insert into #tmp(id) values (1)'
I knew this had to cause some error because there's no space or semicolon between the create and insert statements but didn't know the exact error that would be produced. I've never seen any of the other specific errors so I guessed correctly but for the wrong reason.
December 10, 2010 at 3:27 am
cengland0 (12/10/2010)
This should produce the same as (Notice no spacing between two commands):
Set @strSQL = 'Create table #tmp (id int)Insert into #tmp(id) values (1)'I knew this had to cause some error
Except it doesn't.
If you copy/paste the above in an SSMS query window and hit execute, you get "Command(s) completed successfully."
December 10, 2010 at 6:10 am
Hugo Kornelis (12/10/2010)
Except it doesn't.If you copy/paste the above in an SSMS query window and hit execute, you get "Command(s) completed successfully."
Exactly. I try to answer the questions without executing the code so I thought the missing space would cause an error.
Remember, there are so many trick QOTD's so I'm always looking for the trick. I thought I found the trick and it was that a space was missing. The lack of the space actually had nothing to do with the error but I thought it did so I selected the error message I thought would have been generated and got it right but for the wrong reason.
December 10, 2010 at 6:54 am
Got it right for the wrong reason, learned something. Thanks.
December 10, 2010 at 7:16 am
Oleg Netchaev (12/9/2010)
... In other words, if create temp table part were moved out then the temp table would be visible to the exec batch and after, i.e.
if object_id('tempdb.dbo.#tmp') is not null drop table #tmp;create table #tmp (id int);
declare @sql varchar(2000);
set @sql = 'insert into #tmp(id) values (1);';
exec (@sql);
select * from #tmp;
results in
id-----------
1
Oleg
Another way to get it to work is to put it all on the inside of the dynamic SQL like this:
Declare @strSql varchar(2000);
Set @strSql = '';
Set @strSql = @strSql + 'Create table #tmp (id int);';
Set @strSql = @strSql + 'Insert into #tmp(id) values (1);';
Set @strSql = @strSql + 'Select * From #tmp;';
Exec (@strSql);
December 10, 2010 at 8:34 am
cengland0 (12/10/2010)
I thought I found the trick and it was that a space was missing. The lack of the space actually had nothing to do with the error but I thought it did so I selected the error message I thought would have been generated and got it right but for the wrong reason.
I'll admit that I expected the missing space to be the issue too, when I saw the question in the daily mail. But when I went to the sit, saw the answer options, and missed any option that I could relate to this missing space issue, I realized that apparently either the missing space is not a problem, or the author accidentally forgot the space and didn't realize. So I answered the question, then copied and executed the code to check that indeed, no space is required here.
December 10, 2010 at 10:21 am
Hugo Kornelis (12/10/2010)
da-zero (12/10/2010)
(but it surprises me that apparently 78% -for the moment- know the exact error message!)Well, I don't know about others, but for me this is one of the more common errors I see when developing. Or when copying and pasting code snippets from internet support forums.
Hugo makes a good point. I am sure more than 78% of us have submitted or executed code from this very web site that has returned this error.
<shhhhhh>
But I still always check my work by running the code.
December 10, 2010 at 10:44 am
Since the error is due to the #tmp table no longer being in scope once the EXEC() is done, one could fix this to work by making the table global and adding an explicit DROP after its last use. This leaves the table available to the SELECT.
Declare @strSql varchar(2000)
Set @strSql = ''
Set @strSql = @strSql + 'Create table ##tmp (id int)' -- Now "##tmp', not "#tmp'
Set @strSql = @strSql + 'Insert into ##tmp(id) values (1)'
Exec (@strSql)
Select * from ##tmp
drop table ##tmp
Of course, in practice, one must be careful in naming global temp tables to avoid possible collisions with unrelated processes.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply