SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What is the result of the following query


What is the result of the following query

Author
Message
Ajit-297150
Ajit-297150
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 77
Comments posted to this topic are about the item What is the result of the following query
Oleg Netchaev
Oleg Netchaev
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2183 Visits: 1817
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
tommyh
tommyh
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2508 Visits: 2000
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
Nakul Vachhrajani
Nakul Vachhrajani
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3242 Visits: 2149
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
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60649 Visits: 13297
Nice question, thanks.

(but it surprises me that apparently 78% -for the moment- know the exact error message!)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
tommyh
tommyh
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2508 Visits: 2000
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 :-P

Like i wrote a bit up... the second error message about not being able to find #tmp shouldnt have been there.

/T
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18497 Visits: 12426
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).


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
cengland0
cengland0
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2222 Visits: 1300
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.
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18497 Visits: 12426
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."


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
cengland0
cengland0
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2222 Visits: 1300
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search