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.
Syed Asrar Ali