Temporary procedures behave the same as a normal stored procedure but as the name suggests it exists temporarily on the database server. There are two kinds of temporary procedures, local and global.

These procedures are saved in the tempdb database.


Creating Local temporary stored procedure: This stored procedure name begins with a hash (#) sign and can be accessed in the current session only. This procedure is automatically dropped when the session is closed.

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

When I execute it in the same session then the output is as below:
Keep the session window 52 open and execute it in a different session window then it throws an error as shown below:
Creating a global temporary stored procedure: This stored procedure name begins with a double hash (##) sign and can be accessed to all sessions and is dropped when the session of the user that created it is closed.

For Example in the below image I have created a global temporary Stored Procedure in the query window with session Id: (54).
When I execute it in the same session then the output is as below:
Keep the session window 54 open and execute it in a different session then the output is as below:
When you close query window 54 in which you create a global temporary procedure and try to execute it in another query window. You will get an error like an object not found because the session is now closed.

Temporary stored procedures get saved in tempdb as shown in below image
the above image displays a Stored Procedures saved in the tempdb database while the session is active. As different users from the different sessions can create a local temporary stored procedure with the same name so to differentiate between the procedure name, SQL Server adds long postfixes in local temporary objects.

You can call local and global temporary stored procedures from any database because they are created in tempdb and SQL Server does not add schema and database name in case of temporary objects.