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

Related Posts