A less known feature of SQL Server are temporary stored procedures, which, as their name says, are fleeting database objects. They are created just like any other stored procedure, the only difference being in how you name them: prefixed with # for local usage or with ## for global usage.

‍
Note: one limitation of temporary stored procedures is that they cannot be CLR.

There’s two types of temporary stored procedures:

·       Local, prefixed with # have the lifespan of the session where they are created and they can only be used within that session.

·       Global, prefixed with ##, have the lifespan of the session where they are created but can be used in any other session that you create.

‍

The syntax for creating a temporary stored procedure is what you’d expect:

CREATE PROC #LocalProcedure

AS

/* Your code */

CREATE PROC ##GlobalProcedure

AS

/* Your code */

‍

Uses of Temporary Stored procedures

The reasons why you would want to use a temporary stored procedure are the same for which you would want to use a persisted stored procedure - you want to take advantage of code reuse and execution plan caching features.

There’s several use cases for temporary stored procedures.

1. They are ideal in cases when you do not have create permissions on the target database or you do not want to have a persisted stored.
2. For development or maintenance, you might have complex tasks or checks that you want to execute. For this purpose, you probably have a set of SQL queries that can perform different tasks, like for example - a query that shows all the active running queries on a database. When these queries become complex you often want to parametrize. For this purpose, you can leverage the capabilities of temporary stored procedures by encapsulating the logic of the queries inside of them. Once that’s done, you can execute your procedures with different parameters.

‍

Using temporary stored procedures with SSIS

Temporary stored procedures are ideal when dealing with complex data gathering for which a simple select or a view would not suffice –this can be either because of complex business logic or for performance considerations.

In this case you can use global temporary stored procedures for splitting the queries and use temporary tables or table variables to prepare the data for the final query. All without leaving a trace on the server because the procedures will be gone as soon as your session ends– or in this case, when the SSIS package completes it’s execution.

If you have ever used a temporary table in SSIS this process will look familiar.

Step 1. Configure and Execute SQL Task to create the global temporary procedure

First step that we need to do is to add an Execute SQL Task into the Package.

Inside the Execute SQL Task add your SQL statement and close the designer.

Step 2. Configure the OLE DB Source to use the result of the temporary stored procedure

Before the next part, run the create procedure statement in SSMS to avoid errors when creating the OLE DB Source task.

Now that you have the temporary procedure in the database add a Data Flow task in the package.

In the Data Flow Task we add an OLE DB source task and from here we can make a call to the temporary stored procedure that we just created.

Before you hit OK you can check the Columns to see that they match what you expect. If they do, hit OK and the build part of the SSIS package is ready.

Step 3. Configure properties

Before we can use this package there are a few properties that need to be changed.

The first property is the Connection Manager property. Right click your connection and go to properties and change the value for RetainSameConnection to True.

WHY - the RetainSameConnection property must be changed to True because the default value makes SSIS open a new connection to the database for each task that uses that connection. We do not want the connection to be closed, because the moment the connection is closed the temporary procedure is deleted and cannot be used in the subsequent tasks.

The second property we need to change is the Delay validation- this needs to be set on True for the Data Flow Task.

WHY - when we develop an SSIS package we create the temporary procedure using SSMS, that is to avoid errors when creating the OLE DB Source component. When the package is ready, the temporary procedure will not exist in the database until its created by an Execute SQL Task. What delaying the validation means is that when the package is started - until the task is reached - SSIS will not validate the components. For example, it will not check if our temporary procedure exists. By the time it reaches the OLE DB Source, the procedure exists because it was created in a previous step and SSIS will validate it.

With these changes we can go ahead and run the SSIS package successfully. This way you can leverage complex SQL code in a black box without having persisted code on the source database.

Thank you for reading this and I hope that I gave you some ideas on how you can use a temporary stored procedure in your day to day maintenance tasks or in your SSIS packages when dealing with complex data extraction queries.

‍