Temp Table And Stored Proc Compilation

Beginner question but I believe there is lot of confusion still exists as this behavior changed between SQL Server versions between SQL 2000 and SQL 2005/later. SQL Server 2000 Ha

Solution 1:

You are entirely correct about SQL 2000. For SQL 2005, the statements that use the temp table are compiled seperately from the stored procedure and cached so that they can be reused.

See Temporary Tables, Table Variables and Recompiles on the SQL Programmability & API Development Team Blog. Scroll to the part that starts with "When the stored procedure DemoProc1 is compiled, the insert and select query are not compiled".