SQL SERVER – Creating Temporary and Global Temporary Stored Procedures

During recent Comprehensive Database Performance Health Check, I had a very interesting situation I encountered where we found a stored procedure which was a root cause of the slowness. What we needed to do is to change the code and test if our new stored procedure is better than previous stored procedure or not. One of the options was to create a new stored procedure by renaming the older stored procedure and keep on testing. However, there was a small risk if we forget to drop the newly created stored procedure there will be junk created in the database. Let us see how we can avoid this situation by creating temporary or 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.

During performance tuning consultations, whenever I have to modify as a stored procedure, instead of creating a regular stored procedure, I always craete a temporary stored procedure and test my modifications. This way, I do not have to worry about the leaving any junk code in the database. Whenever I close my sessions in SSMS, it automatically drops the temporary stored procedures.

Let us see an example of the same.

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: Pinal Dave (https://blog.sqlauthority.com)