Database Reference
In-Depth Information
GO
CREATE PROC dbo.TestProc
AS
CREATE TABLE #ProcTest1 (C1 INT); --Ensure table doesn't exist
SELECT *
FROM #ProcTest1; --Causes recompilation
DROP TABLE #ProcTest1;
GO
EXEC dbo.TestProc; --First execution
EXEC dbo.TestProc; --Second execution
Since a local temporary table is automatically dropped when the execution of a stored procedure finishes,
it's not necessary to drop the temporary table explicitly. But, following good programming practice, you can drop
the local temporary table as soon as its work is done. Figure
17-10
shows the Extended Events output for the
preceding example.
Figure 17-10.
Extended Events output showing a stored procedure recompilation because of a local temporary table
