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.

SQL SERVER - Creating Temporary and Global Temporary Stored Procedures tempsp-800x258

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.

Solarwinds

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)

Solarwinds
, , ,
Previous Post
SQLAuthority News – 3 Performance Tuning Presentations at SQLBits 2018 – London
Next Post
SQL SERVER – Identify Version of SQL Server from Backup File

Related Posts

7 Comments. Leave new

  • No I haven’t used temp SP so far.
    I will use going forward when in need.

    Reply
  • I’d rather have it persisted in the database and remember to clean up after myself rather than risk losing all my work just because my session was closed accidentally or due to connection loss etc.

    Reply
  • How’s about access right? Could Global SP be executed from other sql login account?

    Reply
  • I tried to do the same once when I was analysing one SP but it was not allowing to create local or global SP ..Not sure why ..will try again

    Reply
  • Global Store procedure can be executed only for database on which it was created. If you access it from another database it works but returns data for database on which it was created. So if you want to execute the same code for all databases on server it won’t work :(
    If you type:
    USE somedb ;
    Exec ##procedure
    it will not work corectly
    Also inside ##procedure you can not type”USE somedb”

    Reply
  • Thanks Pinal for sharing, I has been working on the SQL since last 10 years but this was not known to me. this is quit useful for testing & validation perspective.

    Reply

Leave a Reply

Menu