Nested Temporary Tables

Question

Nested Temporary Tables

Viewing 15 posts - 16 through 30 (of 64 total)

You must be logged in to reply to this topic. Login to reply

BarbW SSCarpal Tunnel Points: 4759 More actions · Answer 1

Tricky question/answer -not sure if I would ever use the information learned with this one either. 😎

Richard M Karpel Ten Centuries Points: 1221 More actions · Answer 2

The problem is that when you run the code as is, you will not get to Proc3; If you do, you get the answer that you are looking for. But since proc2 is run from proc1, and the temp table is still in actual scope, you will get Answer C, which is an error message and stops the processing at the insert inside of Proc2. Thus when Proc3 is executed and the temp table is not defined and you would get the answer that you list as correct.

Now, I read the section highlighted in BOL, and I expected to get the not defined error message, but I would need to look further and see why, #test1 still was available to Proc2. It should have created a new version of #test1, but it did not, it saw the version created within Proc1.

So for everyone that got the mismatch column error, what is different about our setup then the one that should match the BOL.

I am running SS2K8 Developer Edition, with the latest patches, but not R2.

Rick Karpel

Cliff Jones SSChampion Points: 10547 More actions · Answer 3

A very interesting question but I am not sure the answer is completely correct? Seems like the contents of the temp table would be within the scope of the last stored procedure to create the temp table. So 10 records.

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 4

Interesting question Wayne - Thanks!

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Paul Peeters (12/16/2010)
The real error that is displayed is "Incorrect syntax" because there is a non-matching closing parenthesis in the select statement in dbo.Proc1 😛

Look more closely. At first glance it appears that way but the parentheses match.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 6

Cliff Jones (12/16/2010)
A very interesting question but I am not sure the answer is completely correct? Seems like the contents of the temp table would be within the scope of the last stored procedure to create the temp table. So 10 records.

The way I think of this is, a single temp table can't span multiple stored procedures. I've also learned to be careful not to nest stored procedures which use temp tables with the same names since it tends to confuse SQL Server.

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

Cliff Jones SSChampion Points: 10547 More actions · Answer 7

mtillman-921105 (12/16/2010)
Cliff Jones (12/16/2010)
A very interesting question but I am not sure the answer is completely correct? Seems like the contents of the temp table would be within the scope of the last stored procedure to create the temp table. So 10 records.
The way I think of this is, a single temp table can't span multiple stored procedures. I've also learned to be careful not to nest stored procedures which use temp tables with the same names since it tends to confuse SQL Server.

I agree, it is a practice that you should avoid.

Paul Peeters SSC Enthusiast Points: 127 More actions · Answer 8

CirquedeSQLeil (12/16/2010)
Paul Peeters (12/16/2010)
The real error that is displayed is "Incorrect syntax" because there is a non-matching closing parenthesis in the select statement in dbo.Proc1 😛
Look more closely. At first glance it appears that way but the parentheses match.

Oops, you're right :ermm:

Carlo Romagnano SSC-Insane Points: 22666 More actions · Answer 9

Richard M Karpel (12/16/2010)
Now, I read the section highlighted in BOL, and I expected to get the not defined error message, but I would need to look further and see why, #test1 still was available to Proc2. It should have created a new version of #test1, but it did not, it saw the version created within Proc1.

That's wrong!

The second proc create successfully a new temp table, but the error is generated during the insert command because the number of column is different from proc 1 and proc 2 and the syntax checker fail compiling the insert.

Verify it adding a

[sql]select * from sys.objects[/sql]

after the create table in the second proc.

You will see the two temp table.

Rune Bivrin SSCrazy Eights Points: 8509 More actions · Answer 10

Richard M Karpel (12/16/2010)
Now, I read the section highlighted in BOL, and I expected to get the not defined error message, but I would need to look further and see why, #test1 still was available to Proc2. It should have created a new version of #test1, but it did not, it saw the version created within Proc1.
So for everyone that got the mismatch column error, what is different about our setup then the one that should match the BOL.

BOL doesn't say you'll get a "not defined error". It says the behaviour is undefined. So you might get a column mismatch error or it might run without a hitch. SQL Server makes no promises, and anything you deduce by running the code might well break with the next service pack.

I got the column mismatch error same as everyone else when I tried a variation on the code in the QOTD. The point is I didn't really expect that error and frankly I think it's wrong in a purist sense. I should have gotten some kind of "table already exists" error, and that I didn't just shows that the SQL Server team thinks the use is so contrived and stupid that it doesn't even warrant ensuring predictable handling.

Just because you're right doesn't mean everybody else is wrong.

SanDroid SSChampion Points: 10068 More actions · Answer 11

malleswarareddy_m (12/16/2010)
I got 10 rows.I am using sqlserver 2008.
I think the author answer is correct.It s depends

I would say it really just depends on how well you type example, and the version of SQL server.

Would you mind showing the code you ran (you had to of retyped the code from the image) and the output DBCC USEROPTIONS?

I have ran this code on 4 servers already exactly as it is in the example and here are the results.

SQL 2000 - Code did not execute because of ROW_NUMBER()

SQL 2005 - Error in column statement.

SQL 2008 - Error in column statement.

SanDroid SSChampion Points: 10068 More actions · Answer 12

Rune Bivrin (12/16/2010)

BOL doesn't say you'll get a "not defined error". It says the behaviour is undefined.
I got the column mismatch error same as everyone else when I tried a variation on the code in the QOTD.

Exactly... This is just another example of what happens when you submit code about a topic you don't uderstand as a QOTD.

mtassin SSC-Insane Points: 23099 More actions · Answer 13

Daniel Bowlin (12/16/2010)
Interesting question, even more interesting explanation. Hmmm. I guess my take away from this question, is don't use temp tables in stored procedures.

That would be a horrible take from this.

The proper take is to not use simply named temp tables in nested stored procedures.

Using temp tables in stored procedures is a good practice in many many issues.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 14

SanDroid (12/16/2010)

Rune Bivrin (12/16/2010)
BOL doesn't say you'll get a "not defined error". It says the behaviour is undefined.
I got the column mismatch error same as everyone else when I tried a variation on the code in the QOTD.
Exactly... This is just another example of what happens when you submit code about a topic you don't uderstand as a QOTD.

SanDroid, please be a little more respectful because Wayne is one of the smartest SQL Server gurus around. Have you seen how he answers questions on this site? His knowledge runs deep, and yet he explains answers without getting excessively technical.

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

SanDroid SSChampion Points: 10068 More actions · Answer 15

mtassin (12/16/2010)
Daniel Bowlin (12/16/2010)
Interesting question, even more interesting explanation. Hmmm. I guess my take away from this question, is don't use temp tables in stored procedures.
That would be a horrible take from this.

Using #temp tables in stored procedures is something that is neccessary.

Even if you declare a table variable in a stored procedure and use that, it creates a temp table in the TEMPDB while in scope.

The BOL is trying to say that you should not use TEMP tables of the same name for differant purposes in the same scope. Depending on how they are created, used, and executed determines on wrong your results will be.

So #temp tables = GOOD

Nesting the same #temp table in more than one SPROC = BAD