Temporary Stored Procedures in SQL Server 2008

Today I discovered a small feature of Temporary Stored Procedures in SQL Server.

Temporary Stored Procedures are similar to normal Stored Procedures, but as their name suggests, have a fleeting existence. There are two kinds of temporary Stored Procedures, local and global. Temporary Stored Procedures are created just like any other SP but the name must be prefixed with a hash (#) for a local temporary SP and two hashes (##) for a global temporary Stored Procedure.

A local temporary Stored Procedure is available only in the current session and is dropped when the session is closed or for a different session.

For Example: I have created a temporary Stored Procedure in a query window with session Id: (75).



When I execute the same session then the output is as below:



When I execute a different session the error is shown below:



A global temporary Stored Procedure is visible to all sessions and is dropped when the session of the user that created it is closed.

For Example: I have created a temporary Stored Procedure in the query window with session Id: (68).




When I execute the same session the output is as shown below:



When I execute a different session the output is as shown below:



If there are any executing versions of the global Stored Procedure when the creator session is closed.

Apart from local and global temporary Stored Procedures, you can also create a regular stored process in the system database tempdb.

You should see this in Management Studio:



This picture displays a Stored Procedures in the tempdb database while the session is active. Notice the long postfix that SQL Server automatically adds to the local temporary Stored Procedure. SQL Server adds some postfixes to local temporary objects to differentiate objects with the same name from different sessions.