December 16, 2010 at 9:11 am
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.
Oh but it most certainly can span multiple stored procedures.
For instance if a master stored procedure creates a temp table and then executes two child stored procedures the temp table is available to both of them. Likewise this 3 layer nested stored procedure.
December 16, 2010 at 9:20 am
mtassin (12/16/2010)
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.
Oh but it most certainly can span multiple stored procedures.
For instance if a master stored procedure creates a temp table and then executes two child stored procedures the temp table is available to both of them. Likewise this 3 layer nested stored procedure.
But I think the trouble comes in when you're creating different tables with the same name in different nested procedures. When you execute Select * from #test1, how is SQL supposed to know which table you mean?
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
December 16, 2010 at 9:28 am
I did not mean to imply that BOL told me what the error would be, and since I was writing this quickly, after testing and breaking up the queries into verifiable pieces, all I meant to say is that I expected that the temp table would not exist when I ran proc2, and that the results expected by running Proc3 would never be able to occur, with my current setup, since I received an error before proc2 completed.
December 16, 2010 at 9:31 am
mtillman-921105 (12/16/2010)
mtassin (12/16/2010)
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.
Oh but it most certainly can span multiple stored procedures.
For instance if a master stored procedure creates a temp table and then executes two child stored procedures the temp table is available to both of them. Likewise this 3 layer nested stored procedure.
But I think the trouble comes in when you're creating different tables with the same name in different nested procedures. When you execute Select * from #test1, how is SQL supposed to know which table you mean?
Oh that's definitely the problem. But saying that they can't span is improper. As in most cases with SQL the proper answer is usually "it depends".
Saying that you shouldn't use simply named temp tables when spanning across stored procedures would be a proper statement. You certainly *can* do it, but you very likely shouldn't. 🙂
December 16, 2010 at 9:32 am
mtillman-921105 (12/16/2010)
But I think the trouble comes in when you're creating different tables with the same name in different nested procedures. When you execute Select * from #test1, how is SQL supposed to know which table you mean?
SQL knows to use the table in scope and when faced with a create statement for an existing temp table that is in scope the create statement is ignored.
The only way to change the output of the code when being executed on the same server is to change the order of execution.
December 16, 2010 at 9:41 am
SanDroid (12/16/2010)
mtillman-921105 (12/16/2010)
But I think the trouble comes in when you're creating different tables with the same name in different nested procedures. When you execute Select * from #test1, how is SQL supposed to know which table you mean?
SQL knows to use the table in scope and when faced with a create statement for an existing temp table that is in scope the create statement is ignored.
The only way to change the output of the code when being executed on the same server is to change the order of execution.
Yes, the scope should determine it, but naming them the same name is not a good practice. (It's also confusing to us mere humans.)
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
December 16, 2010 at 9:44 am
Thanks for the question. I learned from this question.
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
December 16, 2010 at 10:34 am
Okay, I messed up. I posted the wrong code (the posted code was run to get the error message for the QotD). Then I went and gave the explanation based on that wrong code... 🙁 Steve, please give credit to all that selected the error message as the answer.
FYI, this is the code that I meant to post (and it DOES run on SQL 2000+):
USE tempdb
GO
SET NOCOUNT ON;
if OBJECT_ID('dbo.QotD1','P') IS NOT NULL DROP PROCEDURE dbo.QotD1;
if OBJECT_ID('dbo.QotD2','P') IS NOT NULL DROP PROCEDURE dbo.QotD2;
if OBJECT_ID('dbo.QotD3','P') IS NOT NULL DROP PROCEDURE dbo.QotD3;
GO
CREATE PROCEDURE dbo.QotD3 AS
SELECT 'QotD3', * FROM #QotD;
GO
CREATE PROCEDURE dbo.QotD2 AS
CREATE TABLE #QotD (RowID INT IDENTITY, name sysname);
INSERT INTO #QotD (name) SELECT TOP 5 name FROM master.dbo.sysobjects ORDER BY name;
EXECUTE dbo.QotD3;
GO
CREATE PROCEDURE dbo.QotD1 AS
CREATE TABLE #QotD (RowID INT IDENTITY, name sysname, RowGUID UNIQUEIDENTIFIER);
INSERT INTO #QotD (name) SELECT TOP 10 name FROM master.dbo.sysobjects ORDER BY name DESC;
EXECUTE dbo.QotD2;
GO
EXECUTE dbo.QotD1;
if OBJECT_ID('dbo.QotD1','P') IS NOT NULL DROP PROCEDURE dbo.QotD1;
if OBJECT_ID('dbo.QotD2','P') IS NOT NULL DROP PROCEDURE dbo.QotD2;
if OBJECT_ID('dbo.QotD3','P') IS NOT NULL DROP PROCEDURE dbo.QotD3;
GO
Please note that even though the temp tables that are created in the different procedures have different structures (one has an additional RowGUID column), you don't get that error - because the RowGUID column allows nulls, and it wasn't explicitly stated in the insert statement. (And if it had a default on it, data still would have been put into that column.) As long as the DML statements don't reference columns not in both #temp tables, it will succeed (in contrast to what BOL says about the structure needing to be the same, they don't have to be identical - you just can't reference columns that are not in both of the #temp tables in your DML statements).
Due to the non-deterministic manner in which SQL will work with these nested, identically named temporary tables, it's actually possible that procedure QotD3 could return 0, 5, 10 or 15 records. QotD1 will insert 10 records into the #QotD table it created. In QotD2, it will insert 5 records into either the #QotD table that it created, or the one created in QotD1. So you would have either 5 records or zero in the #QotD table that QotD2 creates; and either 10 or 15 records in the #QotD table that QotD1 creates. When you get to QotD3 to select the data, it will get the data from one of the tables - so you might have 0, 5, 10 or 15 records returned.
In the testing that I've done with the above code, I have not seen any inserts into any table other than the most recently created #QotD table, and the selects have always been from the most recently created #QotD table. But I have previously seen where things were flipping back and forth.
The whole point of this was to show that if you happen to have two identically named #temporary tables, you cannot depend on anything in terms of which one you are manipulating the data on. (It would be nice if there was a test to show it flipping back and forth, but then it probably wouldn't be undefined which one would be the one being manipulated.)
I'm sorry to all about messing this up. However, I still hope that you have learned from this QotD!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 16, 2010 at 10:38 am
Ditto for SQL Server 2008 that I have. I lost the points for this question.
Bobby VK (12/16/2010)
I typed the code into my SSMS as I wasn't sure of the answer, and I definitely got the error complaining about the number of columns. I could be doing something wrong? I have SQL 2008 developer edition.
December 16, 2010 at 10:41 am
I am on the same page and want my points back too...
Carlo Romagnano (12/16/2010)
The QOtD asks for what is the output of a specific script and this generates this error:Insert Error: Column name or number of supplied values does not match table definition.
and not all possible similar script that COULD generate another kind of error.
I want back my points.
December 16, 2010 at 10:50 am
Ditto...
John McC (12/16/2010)
hmm, I getMsg 213, Level 16, State 1, Procedure Proc2, Line 4
Column name or number of supplied values does not match table definition
SQL 2008
Win 2008 R2
December 16, 2010 at 10:54 am
I have awarded back points and edited the question. For now I am disqualifying this question.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 16, 2010 at 10:54 am
da-zero (12/16/2010)
Nice question and an important point, but I have executed the code a 100 times or more and I always get the error.
While executing the T-SQL a mere 40 times on an instance of SQL 2008 Developer Edition, and receiving the same answer, each and every time, and knowing that BOL at times contains broad general statements not always sufficient in detail, I selected what the QOD considered an incorrect answer.
But most importantly I think I learned something that should be avoided in my code, and learning is more important than the points awarded.
So to WayneS - thanks for a learning experience.
December 16, 2010 at 11:01 am
Steve Jones - SSC Editor (12/16/2010)
I have awarded back points and edited the question. For now I am disqualifying this question.
Steve - Thanks so much. You had done this in the past for questions that required less ESP to answer correctly. I am not suprised that you did it for this one.
December 16, 2010 at 11:17 am
WayneS (12/16/2010)
Okay, I messed up. I posted the wrong code (the posted code was run to get the error message for the QotD). Then I went and gave the explanation based on that wrong code... 🙁 Steve, please give credit to all that selected the error message as the answer.FYI, this is the code that I meant to post (and it DOES run on SQL 2000+):
(code attached - Websense at work won't allow it - I'll move it into the post tonight)
Please note that even though the temp tables that are created in the different procedures have different structures (one has an additional RowGUID column), you don't get that error - because the RowGUID column allows nulls, and it wasn't explicitly stated in the insert statement. (And if it had a default on it, data still would have been put into that column.) As long as the DML statements don't reference columns not in both #temp tables, it will succeed (in contrast to what BOL says about the structure needing to be the same, they don't have to be identical - you just can't reference columns that are not in both of the #temp tables in your DML statements).
Due to the non-deterministic manner in which SQL will work with these nested, identically named temporary tables, it's actually possible that procedure QotD3 could return 0, 5, 10 or 15 records. QotD1 will insert 10 records into the #QotD table it created. In QotD2, it will insert 5 records into either the #QotD table that it created, or the one created in QotD1. So you would have either 5 records or zero in the #QotD table that QotD2 creates; and either 10 or 15 records in the #QotD table that QotD1 creates. When you get to QotD3 to select the data, it will get the data from one of the tables - so you might have 0, 5, 10 or 15 records returned.
In the testing that I've done with the above code, I have not seen any inserts into any table other than the most recently created #QotD table, and the selects have always been from the most recently created #QotD table. But I have previously seen where things were flipping back and forth.
The whole point of this was to show that if you happen to have two identically named #temporary tables, you cannot depend on anything in terms of which one you are manipulating the data on. (It would be nice if there was a test to show it flipping back and forth, but then it probably wouldn't be undefined which one would be the one being manipulated.)
I'm sorry to all about messing this up. However, I still hope that you have learned from this QotD!
Thanks for the clarification Wayne.
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
Viewing 15 posts - 31 through 45 (of 64 total)
You must be logged in to reply to this topic. Login to reply