SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Ajit Halder / What is the result of the following query / Latest Posts

RE: What is the result of the following query

Danny Ocean — Fri, 01 Apr 2011 04:14:21 GMT

Good Question.

RE: What is the result of the following query

Enigma475 — Tue, 21 Dec 2010 08:10:05 GMT

Very interesting question. Thank you!

RE: What is the result of the following query

SQLRNNR — Tue, 14 Dec 2010 10:33:10 GMT

Thanks for the question.

RE: What is the result of the following query

dfine — Sun, 12 Dec 2010 23:11:51 GMT

good basic question. Declare @strSql varchar(2000)Set @strSql = ''Set @strSql = @strSql + 'Create table #tmp (id int)'Set @strSql = @strSql + 'Insert into #tmp(id) values (1) Select * from #tmp'Exec (@strSql)

RE: What is the result of the following query

Abi Chapagai — Sun, 12 Dec 2010 18:40:15 GMT

Good question. Sometimes it is easy to miss small things and this type of questions will help us to keep up with the tips and tricks.

RE: What is the result of the following query

L' Eomot Inversé — Sun, 12 Dec 2010 10:15:31 GMT

[quote][b]Hugo Kornelis (12/10/2010)[/b][hr] (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).[/quote]No, the case sensitivity makes no difference to the message displayed so even people like me who never use a case-sensitive instance are used to seeing "tempdb" in messages, and never "TempDB".

RE: What is the result of the following query

UMG Developer — Fri, 10 Dec 2010 18:02:45 GMT

Thanks for the question, I have run into this before so I knew it right away.

RE: What is the result of the following query

john.arnott — Fri, 10 Dec 2010 10:44:08 GMT

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. [code="sql"]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 ##tmpdrop table ##tmp[/code]Of course, in practice, one must be careful in naming global temp tables to avoid possible collisions with unrelated processes.

RE: What is the result of the following query

SanDroid — Fri, 10 Dec 2010 10:21:21 GMT

[quote][b]Hugo Kornelis (12/10/2010)[/b][hr][quote][b]da-zero (12/10/2010)[/b][hr](but it surprises me that apparently 78% -for the moment- know the exact error message!)[/quote]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. [/quote]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.

RE: What is the result of the following query

Hugo Kornelis — Fri, 10 Dec 2010 08:34:31 GMT

[quote][b]cengland0 (12/10/2010)[/b][hr]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.[/quote]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.

RE: What is the result of the following query

Dave62 — Fri, 10 Dec 2010 07:16:32 GMT

[quote][b]Oleg Netchaev (12/9/2010)[/b][hr]... 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.[code="sql"]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;[/code]results in [code="sql"]id-----------1[/code]Oleg[/quote]Another way to get it to work is to put it all on the inside of the dynamic SQL like this:[code="sql"]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);[/code]

RE: What is the result of the following query

Daniel Bowlin — Fri, 10 Dec 2010 06:54:45 GMT

Got it right for the wrong reason, learned something. Thanks.

RE: What is the result of the following query

cengland0 — Fri, 10 Dec 2010 06:10:12 GMT

[quote][b]Hugo Kornelis (12/10/2010)[/b][hr]Except it doesn't.If you copy/paste the above in an SSMS query window and hit execute, you get "Command(s) completed successfully."[/quote]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.

RE: What is the result of the following query

Hugo Kornelis — Fri, 10 Dec 2010 03:27:30 GMT

[quote][b]cengland0 (12/10/2010)[/b][hr]This should produce the same as (Notice no spacing between two commands): [code="sql"]Set @strSQL = 'Create table #tmp (id int)Insert into #tmp(id) values (1)'[/code]I knew this had to cause some error[/quote]Except it doesn't.If you copy/paste the above in an SSMS query window and hit execute, you get "Command(s) completed successfully."

RE: What is the result of the following query

cengland0 — Fri, 10 Dec 2010 03:20:51 GMT

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.[code="sql"]Set @strSql = @strSql + 'Create table #tmp (id int)'Set @strSql = @strSql + 'Insert into #tmp(id) values (1)'[/code]This should produce the same as (Notice no spacing between two commands): [code="sql"]Set @strSQL = 'Create table #tmp (id int)Insert into #tmp(id) values (1)'[/code]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.

RE: What is the result of the following query

Hugo Kornelis — Fri, 10 Dec 2010 02:09:09 GMT

[quote][b]da-zero (12/10/2010)[/b][hr](but it surprises me that apparently 78% -for the moment- know the exact error message!)[/quote]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).

RE: What is the result of the following query

tommyh — Fri, 10 Dec 2010 01:20:05 GMT

[quote][b]da-zero (12/10/2010)[/b][hr]Nice question, thanks.(but it surprises me that apparently 78% -for the moment- know the exact error message!)[/quote]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 :-PLike i wrote a bit up... the second error message about not being able to find #tmp shouldnt have been there. /T

RE: What is the result of the following query

Koen Verbeeck — Fri, 10 Dec 2010 01:15:11 GMT

Nice question, thanks.(but it surprises me that apparently 78% -for the moment- know the exact error message!)

RE: What is the result of the following query

Nakul Vachhrajani — Fri, 10 Dec 2010 00:21:59 GMT

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!

RE: What is the result of the following query

tommyh — Thu, 09 Dec 2010 23:43:15 GMT

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

RE: What is the result of the following query

Oleg Netchaev — Thu, 09 Dec 2010 22:03:18 GMT

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.[code="sql"]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;[/code]results in [code="sql"]id-----------1[/code]Oleg

What is the result of the following query

Ajit-297150 — Thu, 09 Dec 2010 21:49:08 GMT

Comments posted to this topic are about the item [B]What is the result of the following query[/B]