SQL Server: Naming Temp Table in Sp’s Being Used at the Same time.

Hi All,

Recently i came across this issue where temp tables in different sp’s having same name gave me an error when they are used together (calling other SP in a SP). I have created the scenario to explain this.

<pre class="brush:sql">---CREATE SP SPROC1 HAVING TEMP TABLE #TABLE1 WITH TWO COLUMNS-----
 
CREATE PROC SPROC1
AS
BEGIN
 
CREATE TABLE #TABLE1(A INT,B INT )
 
INSERT INTO #TABLE1
SELECT 2,3
 
SELECT * FROM #TABLE1
END
GO
 
---CREATE SP SPROC2 HAVING TEMP TABLE #TABLE1 WITH ONE COLUMN-----
 
CREATE PROC SPROC2
AS
BEGIN
 
CREATE TABLE #TABLE1(A INT)
 
INSERT INTO #TABLE1
SELECT 2
 
EXEC SPORC1
 
END
GO
 
EXEC SPROC2
GO
 
 
	 
Msg 213, Level 16, State 1, Procedure sproc1, Line 9
Column name or number of supplied values does not match table definition.</pre>

If you look at the issue it says the tabel in SP sproc1 is having issue with the insert statement,but when you try to execute the sproc1 individually it executes without any errors.

Make sure when you are calling some other Stored Proc in your current Stored Proc they don’t have any common temp tables names.

Any comments to explain this scenario further will be helpful.

Regards

Syed Asrar Ali

Like us on FaceBook  |  Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook