XML FeedsThe other day I was doing some testing and then from the same connection I executed a stored procedure only to be greeted with the following message
Msg 213, Level 16, State 1, Procedure prTestTemp, Line 5
Column name or number of supplied values does not match table definition.
I looked at the proc, hasn't changed in months, I decided to run it from a different window and no problem. I took me a couple of minutes to realize what was going on.
Let's duplicate this here with some code that you can run. Make sure that you run the code all in the same window
First create this stored procedure, do not close this window after creation
- CREATE PROCEDURE prTestTemp
- AS
- CREATE TABLE #temp (id int)
- INSERT #temp VALUES(1)
- SELECT * FROM #temp
- GO
In the same window now create the following temp table
- CREATE TABLE #temp (id int, id2 int)
- INSERT #temp VALUES(1,2)
- SELECT * FROM #temp
Now run the procedure
- EXEC prTestTemp
Here is the error
Msg 213, Level 16, State 1, Procedure prTestTemp, Line 5
Column name or number of supplied values does not match table definition.
Drop the table and we will try again
- DROP TABLE #temp
Run the procedure again
- EXEC prTestTemp
This time there was no error
Let's do another experiment, create the table again
- CREATE TABLE #temp (id int, id2 int)
- INSERT #temp VALUES(1,2)
- SELECT * FROM #temp
Now, let's try modifying the procedure, change create to alter and run it again
- ALTER PROCEDURE prTestTemp
- AS
- CREATE TABLE #temp (id int)
- INSERT #temp VALUES(1)
- SELECT * FROM #temp
- GO
Here is the error
Msg 213, Level 16, State 1, Procedure prTestTemp, Line 5
Column name or number of supplied values does not match table definition.
As you can see, you can't modify the procedure in the same window, copy and paste the code in another window and you won't have a problem.
The reason you run into this because the temporary table is local to your connection, it is not dropped until you close the connection. If you have a temporary table with the same name inside a proc that you try to execute you will run into this problem. One way to avoid this is by not naming a temporary table the same in every stored procedure that you have, for example #temp
BTW, doing something like this is no problem
- CREATE PROCEDURE prTestTemp2
- AS
- CREATE TABLE #temp (id int, id2 int)
- INSERT #temp VALUES(1,2)
- SELECT * FROM #temp
- EXEC prTestTemp
- GO
As you can see both procedure have a temporary table named #temp and you get back two resultsets, one has 1 column, the other one has 2 columns
Just be aware of how this works because you could be scratching your head for hours trying to figure something like this out
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
*begin rant* The best way around this is to always drop temp tables in the proc when finished with them. Otherwise you could leave very large tables "stuck" in tempdb therefore bloating tempdb. *end rant* 
Doesn't help with this scenario, you still get the same problem
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.