Rule Definition
Using temporary tables in nested procedures could be risky when tables are created in the parent stored procedure level and changes are made in the nested level. At the creation moment of the nested procedure, we should create all parent tables. When temporary table is created in the nested procedure, note that the procedure will be recompiled at each execution before SQL Server 2005+. Starting with SQL Server 2005+, recompilation occurs at statement level rather than stored procedure level.

Remediation
Replace temporary tables with queries.

Violation Code Sample

Create proc1  As  Begin   Create table #table1 (col1 integer)  Insert into #table1 select * from toto1   Exec proc2  Drop table #table1  End;  Go  Create table #table1 (col1 integer)  go  Create proc2  As  Begin  /* 1st case */  Select * from #table1;   /* 2nd case*/  Create table #table2 (col1 int);  Insert into #table2 select * from toto2   Select * from #table2;  Drop table #table2;  End;  Go  Drop table #table1  Go

Fixed Code Sample

Create proc1  As  Begin  Exec proc2  End;  Go  Create proc2  As  Begin  /* 1st case , select directly from the final table */  select * from toto1   /* 2nd case, select directly from the final table */  select * from toto2  End;  Go