SQL SERVER – Creating Temporary and Global Temporary Stored Procedures

We all know that SQL Server allows us to create temporary tables but it is not as much known that SQL Server allows us to create temporary as well as global stored procedures.

Temporary Stored Procedures

Here is a simple example of Temporary Stored Procedures.

 -- create a temporary stored procedure

CREATE PROCEDURE #TempSP1
@para1 INT
AS
SELECT @para1
GO
-- execute temporary stored procedure
EXEC #TempSP1 9
GO

When you run the above stored procedure, it actually executes just like a regular stored procedure but it exists in the same session where it is created.
Global Temporary Stored Procedures

If you need to access your Temporary Stored Procedures from a different session, you can also consider Global Temporary stored procedures. For this example, you will have to create a stored procedure name by prefixing with two # signs (##) instead of one # sign.

Here is an example of the Global Temporary Stored Procedure.
-- create a temporary stored procedure
CREATE PROCEDURE ##TempSP1
@para1 INT
AS
SELECT @para1
GO
-- execute global temporary stored procedure from different query window
EXEC ##TempSP1 19
GO
Well, that’s it. I would love to know if you are using Temporary Stored Procedures or not. Please leave a comment.
Reference: Vishal Dhanani (https://sql-server-authority.blogspot.com/