The 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
T-SQL | |
1 2 3 4 5 6 7 8 | CREATE PROCEDURE prTestTemp AS CREATE TABLE #temp (id int) INSERT #temp VALUES(1) SELECT * FROM #temp GO |
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
T-SQL | |
1 2 3 4 | CREATE TABLE #temp (id int, id2 int) INSERT #temp VALUES(1,2) SELECT * FROM #temp |
CREATE TABLE #temp (id int, id2 int) INSERT #temp VALUES(1,2) SELECT * FROM #temp
Now run the procedure
T-SQL | |
1 | EXEC prTestTemp |
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
T-SQL | |
1 | DROP TABLE #temp |
DROP TABLE #temp
Run the procedure again
T-SQL | |
1 | EXEC prTestTemp |
EXEC prTestTemp
This time there was no error
Let’s do another experiment, create the table again
T-SQL | |
1 2 3 4 | CREATE TABLE #temp (id int, id2 int) INSERT #temp VALUES(1,2) SELECT * FROM #temp |
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
T-SQL | |
1 2 3 4 5 6 7 8 | ALTER PROCEDURE prTestTemp AS CREATE TABLE #temp (id int) INSERT #temp VALUES(1) SELECT * FROM #temp GO |
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
T-SQL | |
1 2 3 4 5 6 7 8 9 10 | CREATE PROCEDURE prTestTemp2 AS CREATE TABLE #temp (id int, id2 int) INSERT #temp VALUES(1,2) SELECT * FROM #temp EXEC prTestTemp GO |
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
8 Comments
*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
I ran this and it worked:
CREATE PROCEDURE prTestTemp
AS
CREATE TABLE #temp (id int)
INSERT #temp VALUES(1)
SELECT * FROM #temp
DROP TABLE #temp
GO
CREATE TABLE #temp (id int, id2 int)
INSERT #temp VALUES(1,2)
SELECT * FROM #temp
DROP TABLE #temp
GO
EXEC prTestTemp
DROP PROC prTestTemp
But you dropped the table before running the proc
CREATE TABLE #temp (id int, id2 int)
INSERT #temp VALUES(1,2)
SELECT * FROM #temp
DROP TABLE #temp
GO
EXEC prTestTemp
Try this
CREATE TABLE #temp (id int, id2 int)
INSERT #temp VALUES(1,2)
SELECT * FROM #temp
–DROP TABLE #temp
GO
EXEC prTestTemp
Here is the whole thing to run
CREATE PROCEDURE prTestTemp
AS
CREATE TABLE #temp (id int)
INSERT #temp VALUES(1)
SELECT * FROM #temp
DROP TABLE #temp
GO
CREATE TABLE #temp (id int, id2 int)
INSERT #temp VALUES(1,2)
SELECT * FROM #temp
–DROP TABLE #temp
GO
EXEC prTestTemp
DROP PROC prTestTemp
Right, my rant is that people should be dropping the table when they are finished with it. In this case you are finished with the temp table before you run the proc so you should drop it first.
I don’t mean to detract from what you saying. I could easily see a screnio where you create a temp table, call a proc to pull in more data into a temp table and then process/do something with both temp tables at the same time (which would lead to the issue that you hit on). People should also name their table tables somthing meaningful to help to avoid this issue too (as you stated).
Yep, the only thing is I had a bunch of code so I didn’t even notice that the table was in my query window, it was a bunch of ad-hoc queries, the table was all the way on top