Temporary Stored Procedures in SQL Server

In this article, I highlight a small feature of Temporary Stored Procedures in SQL Server. Temporary Stored Procedures are similar to normal Stored Procedures, and are two kinds of temporary Stored Procedures, local and global.

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

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).
 
SQL1.jpg 
 
When I execute the same session then the output is as below:
 
SQL2.jpg 
 
When I execute a different session the error is shown below:
 
SQL3.jpg 
 
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).
 
SQL4.jpg 
 
When I execute the same session the output is as shown below:
 
SQL5.jpg 
 
When I execute a different session the output is as shown below:
 
SQL6.jpg 
 
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:
 
SQL7.jpg 
 
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.