InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 15:46:27 GMT20http://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspxIt gives an error......Error: "Column name or number of supplied values does not match table definitionSat, 05 Feb 2011 20:15:39 GMTKartik Mhttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]WayneS (12/20/2010)[/b][hr][quote][b]SanDroid (12/20/2010)[/b][hr][quote][b]WayneS (12/20/2010)[/b][hr]6. Table variables are the only available choice to pass a table to a procedure.[/quote]I know XML is not realy a table, but it can be used to pass a table to a procedure as an input parameter. :w00t:[/quote]Yep, prior to being able to pass a table to a procedure, one was limited to using XML or delimited strings. I haven't actually played with passing a table yet, but it does look really promising![/quote]It's relatively simplistic. Create a data type of the table, then make the paramete to the stored proc of the same type. Declare a table variable of the same type, insert a bunch of stuff and then pass it like you would anything else.We needed it because of a generalized search stored proc where collected sets of search criteria were applied in an OR fashion... it worked splendidly.Tue, 21 Dec 2010 07:22:02 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]SanDroid (12/20/2010)[/b][hr][quote][b]WayneS (12/20/2010)[/b][hr]6. Table variables are the only available choice to pass a table to a procedure.[/quote]I know XML is not realy a table, but it can be used to pass a table to a procedure as an input parameter. :w00t:[/quote]Yep, prior to being able to pass a table to a procedure, one was limited to using XML or delimited strings. I haven't actually played with passing a table yet, but it does look really promising!Mon, 20 Dec 2010 14:45:53 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]WayneS (12/20/2010)[/b][hr]6. Table variables are the only available choice to pass a table to a procedure.[/quote]I know XML is not realy a table, but it can be used to pass a table to a procedure as an input parameter. :w00t:Mon, 20 Dec 2010 14:22:57 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]SQLkiwi (12/20/2010)[/b][hr]Both have their strengths and weaknesses, and the more rounded approach is to use each where it is suited. Table variables have many advantages aside from passing TVPs around:1. Named constraints are problematic with #temp tables (another name-collision problem). You cannot name a constraint on a table variable, so again the problem is avoided in all cases.2. Table variables can use user-defined data types and XML schemas defined in the context database. Temporary tables cannot use either, unless they happen to have identical definitions in tempdb, which is inconvenient, and tough to maintain robustly.3. Temporary tables inherit the collation of tempdb, whereas table variables use the context database. It is not all that uncommon for user databases to differ from tempdb in collation, and I do not enjoy resolving collation conflicts.[/quote]FYI, if this is a "contained database" (in SQL 11/Denali), then the temporary table will inherit collation from the current database, and cannot use UDDTs/XML schema/UDFs at all. ([url=http://msdn.microsoft.com/en-us/library/ff929143%28v=SQL.110%29.aspx][u]BOL Reference[/u][/url][quote]4. Data stored in a table variable in the context of a user transaction is not rolled back with the transaction. This can be invaluable, e.g. where we need to log information after a roll back.5. Table variables are the only available choice in function definitions.[/quote]6. Table variables are the only available choice to pass a table to a procedure.[quote]So, I am absolutely not saying that table variables should replace temporary tables every time. I personally prefer to start with a table variable design, and look for reasons that would justify changing to use a temporary table.In practice, I often find that the presence of a temporary object (of sufficient size or complexity to make a table variable a poor choice) an indicator that I am doing something dumb. Specifically, manipulating large amounts of data, creating indexes, relying on statistics to produce a non-trivial plan...all this is work that is performed again and again, on every execution. Often, it indicates that the present overall database design is lacking.Paul[/quote]Mon, 20 Dec 2010 14:09:35 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]SQLkiwi (12/20/2010)[/b][hr]This is true (although table variables can also have a primary key and unique non-clustered indexes, albeit without statistical information).[/quote]Paul,I know you know this - this is just be ensure that anyone else reading this is aware.Just like any other table, a table variable can have one (and only one) clustered index on it. Paul implies that the UNIQUE index on table variables has to be non-clustered; actually one of them can be the clustered index on that table.Mon, 20 Dec 2010 13:55:33 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspxYes. :-)Mon, 20 Dec 2010 09:32:56 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]SQLkiwi (12/20/2010)[/b][hr]In practice, I often find that the presence of a temporary object (of sufficient size or complexity to make a table variable a poor choice) an indicator that I am doing something dumb. Specifically, manipulating large amounts of data, creating indexes, relying on statistics to produce a non-trivial plan...all this is work that is performed again and again, on every execution. Often, it indicates that the present overall database design is lacking.Paul[/quote]Granted,Sometimes we're stuck in the land where we can't make the necessary database structural changes because we're stuck with legacy code that doesn't have an abstracted database access layer (i.e. one that has all the SQL calls as stored procs and not embedded SQL) and we find ourselves constrained in ways that we're stuck with it that way, and temp tables are the only viable work around.Other times it's just part of a massive ETL process, and we need to build working tables to eventually manipulate the data into a way to make the final tables what we want, with some wonky intermediate steps.I personally don't care for table variables, probably due to the all the rumors that developers around me were given. Things like how table variables are memory only and temp tables always go to disk. Those sorts of things have turned me off to using them as my go to tool, and instead I tend to use temp tables first, and use table variables when I need them for specific use.In this case, the end result is very likely "it depends" and/or "to each his own". Provided we're both willing to go to the other tool when necessary, I don't think it's that important. :)Mon, 20 Dec 2010 09:28:39 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]mtassin (12/20/2010)[/b][hr]All I meant is that if you named your temp tables with names like #temp you get what you deserve. Name them with a bit more clarity such as #proc1_temp1 (or with even better names) and you're fine.[/quote]I see. When you say 'a bit more clarity', does that mean 'a bit more uniqueness'? Presumably as a way to avoid collisions? That may reduce the risk, if everyone sticks to the plan, but there are no guarantees. With a table variable, the issue does not arise.[quote]And you can still create indexes on them if necessary, as well as get statistics on them.[/quote]This is true (although table variables can also have a primary key and unique non-clustered indexes, albeit without statistical information). On the other hand, the ability to create extra indexes and statistics can be a double-edged sword. Statistics may need to be maintained, changes in table cardinality and schema may force unnecessary recompilations, and so on.Performing DDL after table creation also prevents SQL Server using some of the caching optimizations for temporary structures present in 2005 onward. Specifically, SQL Server is often able to cache a single data page and an IAM page with the query plan, avoiding the overhead of creation and allocation each time the procedure is executed. Performing DDL after creation defeats this optimization. One cannot perform DDL on a table variable after creation, so again, the issue does not arise.[quote]Until I got to the point of passing tables as parameters into a stored proc, I had given up on table variables as basically useless. Now I have a single use for them, but prefer the greater flexibility of temp tables.[/quote]Both have their strengths and weaknesses, and the more rounded approach is to use each where it is suited. Table variables have many advantages aside from passing TVPs around:1. Named constraints are problematic with #temp tables (another name-collision problem). You cannot name a constraint on a table variable, so again the problem is avoided in all cases.2. Table variables can use user-defined data types and XML schemas defined in the context database. Temporary tables cannot use either, unless they happen to have identical definitions in tempdb, which is inconvenient, and tough to maintain robustly.3. Temporary tables inherit the collation of tempdb, whereas table variables use the context database. It is not all that uncommon for user databases to differ from tempdb in collation, and I do not enjoy resolving collation conflicts.4. Data stored in a table variable in the context of a user transaction is not rolled back with the transaction. This can be invaluable, e.g. where we need to log information after a roll back.5. Table variables are the only available choice in function definitions.So, I am absolutely not saying that table variables should replace temporary tables every time. I personally prefer to start with a table variable design, and look for reasons that would justify changing to use a temporary table.In practice, I often find that the presence of a temporary object (of sufficient size or complexity to make a table variable a poor choice) an indicator that I am doing something dumb. Specifically, manipulating large amounts of data, creating indexes, relying on statistics to produce a non-trivial plan...all this is work that is performed again and again, on every execution. Often, it indicates that the present overall database design is lacking.PaulMon, 20 Dec 2010 09:11:49 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]SQLkiwi (12/18/2010)[/b][hr]I am slightly confused as to what is meant by a 'simply named' temporary table in the preceding discussion.Strikes me this is a great reason to use table variables instead of #temp tables in procedures.Excellent question, Wayne. I don't care about the stuff-up with the code, great work.[/quote]All I meant is that if you named your temp tables with names like #temp you get what you deserve.Name them with a bit more clarity such as #proc1_temp1 (or with even better names) and you're fine.And you can still create indexes on them if necessary, as well as get statistics on them. :)Until I got to the point of passing tables as parameters into a stored proc, I had given up on table variables as basically useless. Now I have a single use for them, but prefer the greater flexibility of temp tables, Mon, 20 Dec 2010 08:09:15 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx=====================================create table #test2 (rowid int)insert into #test2select top 10 row_number () over (order by name), name from master.sys.all_columns======================================The above code will fail because in the 'Table Definition' I have provided 1 column, where as I am trying to insert 2 columns, which is inappropriate.Can anybody, please help me understand where the 'TWEAK' is? I couldn't catch it !! ;-)Mon, 20 Dec 2010 05:41:38 GMTSQL-DBA-01http://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]UMG Developer (12/18/2010)[/b][hr]As long as you understand the consequences and limitations...[/quote]I like to think I have a fairly good grasp, yes.[quote]...and the optimizer can create decent plans with the row estimate it will use for the table variables. (It will always assume that it contains 1 row if I recall correctly.)[/quote]It will estimate one row unless WITH (RECOMPILE) is specified, or there is a statement-level recompile for any of the usual reasons; in either case table cardinality will be available.Sat, 18 Dec 2010 20:06:48 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]SQLkiwi (12/18/2010)[/b][hr]Strikes me this is a great reason to use table variables instead of #temp tables in procedures.[/quote]As long as you understand the consequences and limitations, and the optimizer can create decent plans with the row estimate it will use for the table variables. (It will always assume that it contains 1 row if I recall correctly.)Sat, 18 Dec 2010 15:03:52 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspxI am slightly confused as to what is meant by a 'simply named' temporary table in the preceding discussion.Strikes me this is a great reason to use table variables instead of #temp tables in procedures.Excellent question, Wayne. I don't care about the stuff-up with the code, great work.Sat, 18 Dec 2010 06:20:39 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspxThanks for the question, though it had been edited by the time I got to it.Fri, 17 Dec 2010 14:34:38 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspxThanks for the explanation Wayne.Learned something new and another place to look if results don't meet expectations ;-)Fri, 17 Dec 2010 01:16:00 GMTJohn McChttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspxI saw this after Steve had modified it. I was very confused by the "correct anser" answer, since I thought the option immediately before it was the correct answer. But I chickened out and chose "correct answer".Thu, 16 Dec 2010 18:53:13 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspxIt's nice that we not only learn but get credit at the same time.Thu, 16 Dec 2010 16:43:27 GMTJamie Longstreet-481950http://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]WayneS (12/16/2010)[/b][hr]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.I'm sorry to all about messing this up. However, I still hope that you have learned from this QotD![/quote]Wayne...Thanks again for the explination. We have all had those days. I know I wish there was a way to look at the submited question after the publication notification goes out.I had actually remembered reading about this in one of your many good articles posted at this site and was shocked at how this QOTD contradicted itself, and some of your previous articles.I thought maybe Lord Grevious Error had hacked your account and posted this QOTD.Thu, 16 Dec 2010 11:25:24 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]WayneS (12/16/2010)[/b][hr]I'm sorry to all about messing this up. However, I still hope that you have learned from this QotD![/quote]Well, I did learn from this question, so mission accomplished!And one thing I forgot to mention in my previous post:extra kudos for the nice lay-out of your SQL code. :-) I appreciate it when people put effort in making their code readable.Thu, 16 Dec 2010 11:20:29 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]WayneS (12/16/2010)[/b][hr]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 [i]meant[/i] 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![/quote]Thanks for the clarification Wayne.Thu, 16 Dec 2010 11:17:10 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]Steve Jones - SSC Editor (12/16/2010)[/b][hr]I have awarded back points and edited the question. For now I am disqualifying this question.[/quote]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.Thu, 16 Dec 2010 11:01:49 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]da-zero (12/16/2010)[/b][hr]Nice question and an important point, but I have executed the code a 100 times or more and I always get the error.[/quote]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.Thu, 16 Dec 2010 10:54:35 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspxI have awarded back points and edited the question. For now I am disqualifying this question.Thu, 16 Dec 2010 10:54:09 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspxDitto...[quote][b]John McC (12/16/2010)[/b][hr]hmm, I getMsg 213, Level 16, State 1, Procedure Proc2, Line 4Column name or number of supplied values does not match table definitionSQL 2008 Win 2008 R2[/quote]Thu, 16 Dec 2010 10:50:32 GMTSarbjitSingh.Gugnanihttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspxI am on the same page and want my points back too...[quote][b]Carlo Romagnano (12/16/2010)[/b][hr]The QOtD asks for what is the output of a specific script and this generates this error:[i]Insert Error: Column name or number of supplied values does not match table definition.[/i]and not all possible similar script that COULD generate another kind of error.I want back my points.[/quote]Thu, 16 Dec 2010 10:41:20 GMTSarbjitSingh.Gugnanihttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspxDitto for SQL Server 2008 that I have. I lost the points for this question.[quote][b]Bobby VK (12/16/2010)[/b][hr]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.[/quote]Thu, 16 Dec 2010 10:38:29 GMTSarbjitSingh.Gugnanihttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspxOkay, 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 [i]meant[/i] to post (and it DOES run on SQL 2000+):[code="sql"]USE tempdbGOSET 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;GOCREATE PROCEDURE dbo.QotD3 ASSELECT 'QotD3', * FROM #QotD;GOCREATE PROCEDURE dbo.QotD2 ASCREATE 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;GOCREATE PROCEDURE dbo.QotD1 ASCREATE 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;GOEXECUTE 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[/code]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!Thu, 16 Dec 2010 10:34:08 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspxThanks for the question. I learned from this question.Thu, 16 Dec 2010 09:44:13 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]SanDroid (12/16/2010)[/b][hr][quote][b]mtillman-921105 (12/16/2010)[/b][hr][quote]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?[/quote]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.[/quote]Yes, the scope [i]should[/i] determine it, but naming them the same name is not a good practice. (It's also confusing to us mere humans.)Thu, 16 Dec 2010 09:41:43 GMTmtillman-921105http://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]mtillman-921105 (12/16/2010)[/b][hr][quote]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?[/quote]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.Thu, 16 Dec 2010 09:32:44 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]mtillman-921105 (12/16/2010)[/b][hr][quote][b]mtassin (12/16/2010)[/b][hr][quote][b]mtillman-921105 (12/16/2010)[/b][hr][quote][b]Cliff Jones (12/16/2010)[/b][hr]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.[/quote]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.[/quote]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.[/quote]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?[/quote]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. :)Thu, 16 Dec 2010 09:31:28 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspxI 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.Thu, 16 Dec 2010 09:28:48 GMTRichard M Karpelhttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]mtassin (12/16/2010)[/b][hr][quote][b]mtillman-921105 (12/16/2010)[/b][hr][quote][b]Cliff Jones (12/16/2010)[/b][hr]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.[/quote]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.[/quote]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.[/quote]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?Thu, 16 Dec 2010 09:20:49 GMTmtillman-921105http://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]mtillman-921105 (12/16/2010)[/b][hr][quote][b]Cliff Jones (12/16/2010)[/b][hr]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.[/quote]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.[/quote]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.Thu, 16 Dec 2010 09:11:37 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]mtassin (12/16/2010)[/b][hr][quote][b]Daniel Bowlin (12/16/2010)[/b][hr]Interesting question, even more interesting explanation. Hmmm. I guess my take away from this question, is don't use temp tables in stored procedures.[/quote]That would be a horrible take from this.[/quote]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 = GOODNesting the same #temp table in more than one SPROC = BADThu, 16 Dec 2010 09:04:40 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]SanDroid (12/16/2010)[/b][hr][quote][b]Rune Bivrin (12/16/2010)[/b][hr][quote]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. [/quote]Exactly... This is just another example of what happens when you submit code about a topic you don't uderstand as a QOTD.[/quote]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.Thu, 16 Dec 2010 08:59:37 GMTmtillman-921105http://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]Daniel Bowlin (12/16/2010)[/b][hr]Interesting question, even more interesting explanation. Hmmm. I guess my take away from this question, is don't use temp tables in stored procedures.[/quote]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.Thu, 16 Dec 2010 08:56:19 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]Rune Bivrin (12/16/2010)[/b][hr][quote]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. [/quote]Exactly... This is just another example of what happens when you submit code about a topic you don't uderstand as a QOTD.Thu, 16 Dec 2010 08:50:06 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1035615-1273-1.aspx[quote][b]malleswarareddy_m (12/16/2010)[/b][hr]I got 10 rows.I am using sqlserver 2008.I think the author answer is correct.It s depends[/quote]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.Thu, 16 Dec 2010 08:45:24 GMTSanDroid