SQL Server Temporary Stored Procedures

By: Jared Westover | Updated: 2024-04-29 | Comments | Related: > Stored Procedures

Problem

Need to debug a stored procedure in a Microsoft SQL Server relational database management system (RDBMS) where you don't want to change code or even create a copy of it? For example, you share an environment with other developers and agree not to create procedures on the fly with the term debug in the name. Perhaps you're like me and sometimes forget to clean up after reaching that eureka moment. Once a few weeks pass, you are at a loss as to why your procedure no longer works. After looking at it for an hour, you realize you changed it last month.

Solution

In this tutorial, I'll review a tip from Erik Darling on how to create a copy of a stored procedure in tempdb and add a debug flag. Ideally, the debug flag should already exist, but we don't live in a perfect world. This technique only works in some situations, but look at it as another tool in your toolbox for troubleshooting T-SQL. I hope this tip is as helpful to you as it has been to me over the years.

Temporary Stored Procedures

What is a temporary stored procedure? I'll assume you've created one at some point in your career or at least know what they do. Microsoft defines a stored procedure as a group of one or more Transact-SQL statements or a reference to a Microsoft .NET Framework common runtime language (CLR) method. We're not going to touch on the CLR aspect here.

A temporary stored procedure is the same but doesn't stick around. Have you created a temporary table? The concept is similar: they stick around for the life of your session, well, for the most part.

There are three types of temporary stored procedures:

Global: They are available to all sessions until the session that created it closes.
Local: This type is only available to the session that created it and closes once it closes.
Usual/Standard: You create them in tempdb, and they stay after the session closes. If you've accidentally created a table in master, it's the same concept—we all do it at times.

This article will focus on local, but you could apply the concepts to the other two. I've included the syntax below for creating a local temporary stored procedure.

-- mssqltips.com
CREATE OR ALTER PROCEDURE #GoDoSomething
AS
BEGIN

    SELECT 'Just do it!';

END;
GO

The syntax is similar, except when naming, you include a pound/hash sign (#) as a prefix. Since there isn't much more to creating them than outlined above, let's move on to setting up our demo environment.

Building Our Dataset

With the SQL code below, I'll create three tables for loading customers. The records start in the CustomerStaging table and finally reach the Customer table if they pass all the tests. Also, I included a CustomerTypes table that acts as a dimension.

-- mssqltips.com
USE [master];
GO

IF DATABASEPROPERTYEX('MSSQLTipsDemo', 'Version') IS NOT NULL
BEGIN
    ALTER DATABASE MSSQLTipsDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE MSSQLTipsDemo;
END;
GO

CREATE DATABASE MSSQLTipsDemo; -- SQL Database
GO

ALTER DATABASE MSSQLTipsDemo SET RECOVERY SIMPLE;
GO

USE MSSQLTipsDemo;
GO

CREATE TABLE dbo.CustomerTypes
(
    Id INT NOT NULL,
    TypeName VARCHAR(100) NOT NULL,
    TypeCode VARCHAR(5) NOT NULL,
    CONSTRAINT PK_CustomerTypes_Id
        PRIMARY KEY CLUSTERED (Id)
);
INSERT INTO dbo.CustomerTypes
(
    Id,
    TypeName,
    TypeCode
)
VALUES
(1, 'Nonprofit', 'A001'),
(2, 'Manufacturing', 'B002'),
(3, 'Construction', 'A003'),
(4, 'Real Estate', 'F007'),
(5, 'Hospitality', 'B001'),
(6, 'Green Energy', 'C001'),
(7, 'General', 'D002');

CREATE TABLE dbo.Customers
(
    Id INT IDENTITY(1, 1) NOT NULL,
    CustomerName VARCHAR(100) NOT NULL,
    ActiveDate DATE NOT NULL,
    CustomerType INT NOT NULL,
    IsActive BIT NOT NULL
        DEFAULT (1)
        CONSTRAINT [PK_Customers_Id]
        PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT [FK_CustomerType]
        FOREIGN KEY (CustomerType)
        REFERENCES dbo.CustomerTypes (Id)
);

CREATE TABLE dbo.CustomerStaging
(
    CustomerName VARCHAR(100),
    CustomerType VARCHAR(10),
    CustomerTypeId INT,
    ActiveDate DATE,
    IsActive BIT,
    IsValid BIT
        DEFAULT 0
);
INSERT INTO dbo.CustomerStaging
(
    CustomerName,
    CustomerType,
    ActiveDate,
    IsActive
)
VALUES
('Smith & Associates', 'B001', '01-26-2024', 1),
('Johnson Ent.', 'B002', '01-01-2024', 1),
('Anderson Manufacturing', 'B002', '04-30-2024', 1),
('Wilson & Sons Construction', 'A003', '01-12-2024', 1),
('Thompson Tech Solutions', 'C001', '01-26-2024', 0),
('Martinez Law Firm', 'D002', '02-01-2024', 1),
('Greenfield Healthcare Group', 'T001', '01-01-2024', 1),
('Carter Retail Ventures', 'B001', '03-01-2024', 1),
('Baker Nonprofit Foundation', 'A001', '01-10-2024', 1),
('Cooper Creative Agency', 'F007', '01-10-2024', 0);
GO

The code below is a simple stored procedure that loads customers. I'm sure your environment has some procedures that are several hundred lines for loading data.

-- mssqltips.com
CREATE OR ALTER PROCEDURE dbo.Load_Customers -- Create Procedure Statement
AS
BEGIN

    SET XACT_ABORT ON;
    SET NOCOUNT ON;

    BEGIN TRANSACTION;

    /* Here we set the CustomerTypeId */
    UPDATE cs
    SET cs.CustomerTypeId = ct.Id
    FROM dbo.CustomerStaging cs
        INNER JOIN dbo.CustomerTypes ct
            ON cs.CustomerType = ct.TypeCode;

    /* Here we make the ActiveDate the first Day of Month */
    UPDATE cs
    SET cs.ActiveDate = DATEADD(DAY, 1, EOMONTH(ActiveDate, -1))
    FROM dbo.CustomerStaging cs;

    /* We need to remove periods and commas from CustomerName */
    UPDATE cs
    SET cs.CustomerName = REPLACE(REPLACE(cs.CustomerName, '.', ''), ',', '')
    FROM dbo.CustomerStaging cs;

    UPDATE cs
    SET cs.IsValid = 1
    FROM dbo.CustomerStaging cs
    WHERE cs.CustomerTypeId IS NOT NULL
          AND cs.CustomerName IS NOT NULL;

    /* Here we first try to update any existing customers */
    UPDATE dest
    SET dest.CustomerName = src.CustomerName,
        dest.ActiveDate = src.ActiveDate,
        dest.IsActive = src.IsActive,
        dest.CustomerType = src.CustomerTypeId
    FROM dbo.Customers dest
        INNER JOIN dbo.CustomerStaging src
            ON dest.CustomerName = src.CustomerName
    WHERE src.IsValid = 1;

    /* Insert new customers based on name*/
    INSERT INTO dbo.Customers
    (
        CustomerName,
        ActiveDate,
        CustomerType,
        IsActive
    )
    SELECT CustomerName,
           ActiveDate,
           src.CustomerTypeId,
           IsActive
    FROM dbo.CustomerStaging src
    WHERE src.IsValid = 1
          AND NOT EXISTS
        (SELECT src.CustomerName
         FROM dbo.Customers dest
         WHERE src.CustomerName = dest.CustomerName);

    COMMIT TRANSACTION;

END;
GO

Let's call the stored procedure and see what happens.

-- mssqltips.com
EXEC dbo.Load_Customers; -- procedure name
GO

SELECT * FROM dbo.Customers;

As you can see from our result set from our SQL queries above, we have nine customers in the Customers table, but we are supposed to have 10. You might be quick on your feet and know the issue. Yet, picture dozens of transformation steps instead of three before we get to the insert.

The business needs that final customer; money is on the line. It's time to start the troubleshooting process. In this example, I'll create a temporary local copy of the stored procedure for three reasons:

I want to execute the procedure rather than the individual statements. Could I copy and paste each of the transformation steps? Yes, but in the end, I want to execute the procedure and see it working before I push out code to an upper environment. For me, it's easier to create a local copy.
Could I modify the standard dbo.Load_Customers locally? Sure, but nine times out of 10, I'll forget I made the change, which usually comes back to bite me.
Could I create a non-local copy of the sproc and add _debug to the end? Sure, but like the prior reason, I'll likely forget to clean it up. Like you, I'm busy.

Create a Temporary Stored Procedure

I'll create a local temporary procedure and add debugging logic using the code below. Adding the flag parameter doesn't help much except for demo purposes. Ideally, the flag would already exist. Let's execute the code below and see what the additional select statement tells us.

-- mssqltips.com
CREATE OR ALTER PROCEDURE #Load_Customers @DebugFlag AS BIT = 0
AS
BEGIN

    SET XACT_ABORT ON;
    SET NOCOUNT ON;

    BEGIN TRANSACTION;

    /* Here we set the CustomerTypeId */
    UPDATE cs
    SET cs.CustomerTypeId = ct.Id
    FROM dbo.CustomerStaging cs
        INNER JOIN dbo.CustomerTypes ct
            ON cs.CustomerType = ct.TypeCode;

    /* Here we make the ActiveDate the first Day of Month */
    UPDATE cs
    SET cs.ActiveDate = DATEADD(DAY, 1, EOMONTH(ActiveDate, -1))
    FROM dbo.CustomerStaging cs;

    /* We need to remove periods and commas from CustomerName */
    UPDATE cs
    SET cs.CustomerName = REPLACE(REPLACE(cs.CustomerName, '.', ''), ',', '')
    FROM dbo.CustomerStaging cs;

    UPDATE cs
    SET cs.IsValid = 1
    FROM dbo.CustomerStaging cs
    WHERE cs.CustomerTypeId IS NOT NULL
          AND cs.CustomerName IS NOT NULL;

    /* Add this for debugging */
    IF (@DebugFlag = 1)
    BEGIN
        SELECT 'Debugging' AS Step1,
               *
        FROM dbo.CustomerStaging;
    END;

    /* Here we first try to update any existing customers */
    UPDATE dest
    SET dest.CustomerName = src.CustomerName,
        dest.ActiveDate = src.ActiveDate,
        dest.IsActive = src.IsActive,
        dest.CustomerType = src.CustomerTypeId
    FROM dbo.Customers dest
        INNER JOIN dbo.CustomerStaging src
            ON dest.CustomerName = src.CustomerName
    WHERE src.IsValid = 1;

    /* Insert new customers based on name*/
    INSERT INTO dbo.Customers
    (
        CustomerName,
        ActiveDate,
        CustomerType,
        IsActive
    )
    SELECT CustomerName,
           ActiveDate,
           src.CustomerTypeId,
           IsActive
    FROM dbo.CustomerStaging src
    WHERE src.IsValid = 1
          AND NOT EXISTS
        (SELECT src.CustomerName
         FROM dbo.Customers dest
         WHERE src.CustomerName = dest.CustomerName);

    COMMIT TRANSACTION;

END;
GO

Now, let's execute the temporary stored procedure and review the results.

-- mssqltips.com
EXECUTE #Load_Customers @DebugFlag = 1;
GO

In the screenshot above, the CustomerTypeId is NULL for one of the records, which isn't allowed in our Customers table.

Proposed Fix

Here is where things get interesting. How do we go about fixing this? Maybe our source data isn't returning the correct customer type code. We may need to add logic to our procedure to handle unknown codes. To test it, I'll make the changes to our temporary procedure and rerun it. The primary change below sets any NULL CustomerTypeId to 7, which is the general type.

-- mssqltips.com
CREATE OR ALTER PROCEDURE #Load_Customers @DebugFlag AS BIT = 0
AS
BEGIN

    SET XACT_ABORT ON;
    SET NOCOUNT ON;

    BEGIN TRANSACTION;

    /* Here we set the CustomerTypeId. I'm adding a CASE expression for any NULLs. */
    UPDATE cs
    SET CustomerTypeId = CASE
                             WHEN cs.CustomerTypeId IS NULL THEN
                                 7
                             ELSE
                                 ct.Id
                         END
    FROM dbo.CustomerStaging cs
        LEFT JOIN dbo.CustomerTypes ct
            ON cs.CustomerType = ct.TypeCode;

    /* Here we make the ActiveDate the first Day of Month */
    UPDATE cs
    SET cs.ActiveDate = DATEADD(DAY, 1, EOMONTH(ActiveDate, -1))
    FROM dbo.CustomerStaging cs;

    /* We need to remove periods and commas from CustomerName */
    UPDATE cs
    SET cs.CustomerName = REPLACE(REPLACE(cs.CustomerName, '.', ''), ',', '')
    FROM dbo.CustomerStaging cs;

    UPDATE cs
    SET cs.IsValid = 1
    FROM dbo.CustomerStaging cs
    WHERE cs.CustomerTypeId IS NOT NULL
          AND cs.CustomerName IS NOT NULL;

    /* Add this for debugging */
    IF (@DebugFlag = 1)
    BEGIN
        SELECT 'Debugging' AS Step1,
               *
        FROM dbo.CustomerStaging;
    END;

    /* Here we first try to update any existing customers */
    UPDATE dest
    SET dest.CustomerName = src.CustomerName,
        dest.ActiveDate = src.ActiveDate,
        dest.IsActive = src.IsActive,
        dest.CustomerType = src.CustomerTypeId
    FROM dbo.Customers dest
        INNER JOIN dbo.CustomerStaging src
            ON dest.CustomerName = src.CustomerName
    WHERE src.IsValid = 1;

    /* Insert new customers based on name*/
    INSERT INTO dbo.Customers
    (
        CustomerName,
        ActiveDate,
        CustomerType,
        IsActive
    )
    SELECT CustomerName,
           ActiveDate,
           src.CustomerTypeId,
           IsActive
    FROM dbo.CustomerStaging src
    WHERE src.IsValid = 1
          AND NOT EXISTS
        (SELECT src.CustomerName
         FROM dbo.Customers dest
         WHERE src.CustomerName = dest.CustomerName);

    COMMIT TRANSACTION;

END;
GO

EXECUTE #Load_Customers @DebugFlag = 1;
GO

SELECT * FROM dbo.Customers;
GO

When we look at the Customer table again, we see 10 records—that's what we want. However, what if we need to let another department know that the codes provided were incorrect to fix the issue? After sending an email, they apologized and repopulated the staging table with valid codes. We can move on with our day.

Now, imagine I modified the original procedure instead of using a temporary one. A few weeks pass and I encounter a different issue with loading customers. Since it's been a while, I forgot about the changes. An hour goes by, and for the life of me, I can't figure out where these odd codes are coming from. Something like this has happened to me a few times in my career.

Summary

It's easy to look at the example above and believe you would recall the change. It's like watching an episode of Jeopardy and then rewatching it with friends—they're amazed at your skills. Said another way, it's easy when the answers are at the front of your mind. I review dozens of procedures and statements a day. Keeping all that information in my working memory is a losing battle. Using the temporary stored procedure is like a failsafe; I'm looking out for the future me. Let me know in the comments below if you use temporary stored procedures and other use cases you might have.

Key Points

If you sometimes forget to clean up after yourself, try using temporary stored procedures the next time you need to debug one.
When creating a stored procedure for the first time, add a debug flag and as many select statements as needed. Your future self will thank you.
When it comes to troubleshooting, there is no one size fits all. If you prefer a different method and it works, keep on using it. Let me know what it is in the comments, and maybe I'll start using it.

Next Steps

Harsha Majety wrote an informative article titled Temporary Stored Procedures on the SQLServerCentral website that I suggest checking out to learn more.
Do you want to start adding a debug flag to stored procedures? If so, check out my article, Troubleshoot SQL Server Stored Procedure Execution Problems with Debug Flag.
For a deeper look at debug flags, check out Erik Darling's YouTube video, How I Set Up Debugging In SQL Server Stored Procedures.
Are you interested in creating a CLR stored procedure but don't know where to start? Daniel Farina wrote an article titled SQL Server CLR Stored Procedure Development to get you started.

About the author

Jared Westover is a passionate technology specialist at Crowe, helping to build data solutions. For the past two decades, he has focused on SQL Server, Azure, Power BI, and Oracle. Besides writing for MSSQLTips.com, he has published 12 Pluralsight courses about SQL Server and Reporting Services. Jared enjoys reading and spending time with his wife and three sons when he's not trying to make queries go faster.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2024-04-29