Temporary Stored Procedures – good, old and little-known feature

Almost every T-SQL developer know temporary tables. However, I recently found that only a few people know about the existence of Temporary Stored Procedures. In this short blog post, I’m going to describe this functionality and explain how you can create and use such procedures.

How it works?

Working principle is very similar to the way how temporary tables work. You can create local and global Temporary Stored Procedures.

The scope of local Temporary Stored Procedures is limited to the connection that created it. When the connection is closed then the procedure is dropped. Global Temporary Stored Procedures are available to all SQL Server connections as long as the process that created it exists. When last connection using the procedure is closed, then the procedure is dropped.

Temporary Stored Procedures like temporary tables are created in TempDB database.

Temporary Stored Procedures creation

To create local temporary SP you have to use one number sign (#) before stored procedure name (#usp_TempProc). To create global one you have to use two number signs before the name (##usp_TempProc). Temporary Stored Procedure name cannot exceed 128 characters for the global (including ##) and 116 characters for the local (including #).

-- Create local temporary SP
CREATE PROCEDURE #usp_TempProcLocal
AS
    SELECT @@spid;
GO

-- Create global temporary SP
CREATE PROCEDURE ##usp_TempProcGlobal
AS
    SELECT @@spid;
GO

This is how it looks in Object Explorer.

Looking at local Temporary Stored Procedure you can notice here three things.

Firstly, the name in metadata has added a pseudo-random generated suffix. This is done to avoid name clashes in situations when the same name is used in more than one session. Database engine needs to be able to distinguish between identically-named local temporary stored procedures created in the different sessions. SQL Server reserves 14 characters for this suffix.

Secondly, the name was automatically extended to 128 characters. This is how the full name looks like:

#usp_TempProcLocal______________________________________________________________________________________________________00013507

However, in your session, you still can use this procedure by using the ‘short’ name you specified during creation.

Thirdly, it seems there is a bug in SQL Server Management Studio because Object Explorer displays these objects twice. I created connect item for this issue.

Temporary Stored Procedures execution

You can execute Temporary Stored Procedure like any other regular stored procedure.

As you already know, local Temporary Stored Procedure is visible and accessible in the session that created it. Any attempt to execute it from another session will fail.

You can execute global Temporary Stored Procedure from any session as long as the process that created it exists.

When to use Temporary Stored Procedures?

In various articles on the internet, you can find many different opinions whether Temporary Stored Procedures should be used or not. Some people say there is no use case in which persistent Stored Procedure would not be enough. In my opinion, Temporary Stored Procedures still may be very usable in few cases when you don’t want to create persistent, unnecessary objects:

• Testing – to check some behavior or try solution proposal.
• Diagnostics –  to collect some data in an automated way, when you don’t want to affect target database.
• Deployment – to simplify the creation of complex objects.

What is your opinion? If you want to share it please leave a comment.

-Marek

Share it: