SQL Server Stored Procedure Debug Logic for Execution Problems

By: Jared Westover | Updated: 2022-08-02 | Comments (3) | Related: > Stored Procedures

Problem

Several versions back, Microsoft removed the debug feature from SQL Server Management Studio. I found most people either loved or hated it. I liked it for debugging stored procedures, especially when a WHILE loop was involved. It was nowhere close to debugging functionality in Visual Studio, but it worked most of the time. My fondness for it may be nostalgic because I recall restarting SSMS all the time. Even outside the built-in debugger, you commonly need to see data results before your stored procedure completes. For example, you might have a few temporary tables where data transformation occurs early in the procedure. If something goes wrong, later, you want to know the results of each step leading up to the outcome. That's where a debug flag comes into play.

Solution

This tip will explore creating and using a debug flag in a stored procedure. You can also use a debug flag with ad hoc scripts. I generally leave the debug logic in the script while in production. Once you get the hang of it, adding a debug flag becomes second nature. If implemented correctly, including a flag adds little to no overhead. Did you ever try to run the SSMS debugger in production? I hope you answered a resounding no.

Building Our Environment

Let's get started by creating a dataset. The script below accomplishes our mission. I suggest running all these scripts in a development or local environment. You do not want your DBA asking why there is a SqlHabits database in production.

USE master;
GO

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

CREATE DATABASE SqlHabits;
GO

ALTER DATABASE SqlHabits SET RECOVERY SIMPLE;
GO

USE SqlHabits;
GO

CREATE SCHEMA Sales;
GO

CREATE TABLE Sales.SalesPerson
(
   Id INT IDENTITY(1, 1) NOT NULL,
   EmployeeNumber NVARCHAR(8) NOT NULL,
   FirstName NVARCHAR(500) NULL,
   LastName NVARCHAR(1000) NULL,
   CreateDate DATETIME NOT NULL
       DEFAULT GETDATE(),
   ModifyDate DATETIME NULL,
   CONSTRAINT PK_SalesPerson_Id
       PRIMARY KEY (Id),
   CONSTRAINT UC_SalesPerson_EmployeeNumber
       UNIQUE (EmployeeNumber)
);
GO

CREATE TABLE Sales.SalesOrder
(
   Id INT IDENTITY(1, 1) NOT NULL,
   SalesPerson INT NOT NULL,
   SalesAmount DECIMAL(36, 2) NOT NULL,
   SalesDate DATE NOT NULL,
   CreateDate DATETIME NOT NULL
       DEFAULT GETDATE(),
   ModifyDate DATETIME NULL,
   CONSTRAINT PK_SalesOrder_Id
       PRIMARY KEY (Id),
   CONSTRAINT FK_SalesPerson_Id
       FOREIGN KEY (SalesPerson)
       REFERENCES Sales.SalesPerson (Id)
);
GO

We now have a database with two tables. From the visual below, you can see there is a foreign key from the SalesPerson table to the SalesOrder table.

Adding a Debug Flag

At its core, a debug flag is straightforward. Most of the time, you create one by creating a variable of the bit data type. You can see a simple example below.

CREATE OR ALTER PROCEDURE Sales.InsertSalesOrder @Debug BIT = 0
AS
BEGIN
   IF @Debug = 1
   BEGIN
       SELECT 1;
   END;
END;

I recommend setting the default value to zero or off. Unless you explicitly enable it, the flag will not affect your workload. Setting the default value to zero is also ideal because you don't need to worry about supplying the value when executing the procedure.

Create a Sample SQL Server Stored Procedure

Now for a more helpful example. In the following stored procedure, we insert a row into the SalesOrder table. If the employee number provided doesn't exist, we create the employee.

CREATE OR ALTER PROCEDURE Sales.InsertSalesOrder
    @EmployeeNumber AS NVARCHAR(8),
    @SalesAmount AS DECIMAL(36, 2),
    @SalesDate AS DATE
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        DROP TABLE IF EXISTS #SalesPersonInsert;
        CREATE TABLE #SalesPersonInsert
        (
            Id INT NOT NULL,
            EmployeeNumber NVARCHAR(8) NOT NULL
        );

        DECLARE @SalesPersonId INT;
        SELECT @SalesPersonId =
        (
            SELECT Id
            FROM Sales.SalesPerson sp
            WHERE sp.EmployeeNumber = @EmployeeNumber
        );
        IF (@SalesPersonId IS NULL)
        BEGIN
            INSERT INTO Sales.SalesPerson
            (
                EmployeeNumber
            )
            OUTPUT inserted.Id,
                   inserted.EmployeeNumber
            INTO #SalesPersonInsert
            VALUES
            (@EmployeeNumber);

            SELECT @SalesPersonId =
            (
                SELECT TOP (1) Id FROM #SalesPersonInsert ORDER BY Id
            );

        END;

        INSERT INTO Sales.SalesOrder
        (
            SalesPerson,
            SalesAmount,
            SalesDate
        )
        VALUES
        (@SalesPersonId, @SalesAmount, @SalesDate);

        DROP TABLE IF EXISTS #SalesPersonInsert;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF (@@TRANCOUNT > 0)
            ROLLBACK TRANSACTION;
        THROW;
    END CATCH;

END;
GO

You can see that we are capturing the new employee Id into a temporary table. Doing this allows us to reference it later when inserting the row into the SalesOrder table. The following code will execute the procedure.

EXECUTE Sales.InsertSalesOrder @EmployeeNumber = N'00123456',
                               @SalesAmount = '14.00',
                               @SalesDate = '2022-07-06';
GO

Create a Sample SQL Server Stored Procedure with Debug Flag

When troubleshooting this procedure, it is helpful to see if we insert an employee or use an existing one. Plus, the new or existing salesperson Id. Imagine we get an email from the business user who executes the procedure via the UI, and it’s not behaving as expected. Here is where the debug flag comes into play.

CREATE OR ALTER PROCEDURE [Sales].[InsertSalesOrder]
    @EmployeeNumber AS NVARCHAR(8),
    @SalesAmount AS DECIMAL(36, 2),
    @SalesDate AS DATE,
    @Debug AS BIT = 0
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        DROP TABLE IF EXISTS #SalesPersonInsert;
        CREATE TABLE #SalesPersonInsert
        (
            Id INT NOT NULL,
            EmployeeNumber NVARCHAR(8) NOT NULL
        );

        DECLARE @SalesPersonId INT;
        SELECT @SalesPersonId =
        (
            SELECT Id
            FROM Sales.SalesPerson sp
            WHERE sp.EmployeeNumber = @EmployeeNumber
        );
        IF (@SalesPersonId IS NULL)
        BEGIN
            INSERT INTO Sales.SalesPerson
            (
                EmployeeNumber
            )
            OUTPUT inserted.Id,
                   inserted.EmployeeNumber
            INTO #SalesPersonInsert
            VALUES
            (@EmployeeNumber);
            SELECT @SalesPersonId =
            (
                SELECT TOP (1) Id FROM #SalesPersonInsert ORDER BY Id
            );
        END;

        IF @Debug = 1
        BEGIN
            SELECT Id,
                   EmployeeNumber,
                   'Yes' AS NewEmployee
            FROM #SalesPersonInsert
            UNION ALL
            SELECT Id,
                   EmployeeNumber,
                   'No' AS NewEmployee
            FROM Sales.SalesPerson
            WHERE EmployeeNumber = @EmployeeNumber
                  AND NOT EXISTS
            (
                SELECT Id FROM #SalesPersonInsert
            );
        END;

        INSERT INTO Sales.SalesOrder
        (
            SalesPerson,
            SalesAmount,
            SalesDate
        )
        VALUES
        (@SalesPersonId, @SalesAmount, @SalesDate);

        DROP TABLE IF EXISTS #SalesPersonInsert;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF (@@TRANCOUNT > 0)
            ROLLBACK TRANSACTION;
        THROW;
    END CATCH;

END;
GO

Notice that we will return the row from the SalesPersonInsert table if we enable the debug flag. This data will be helpful if troubleshooting unexpected results.

EXECUTE Sales.InsertSalesOrder @EmployeeNumber = N'00123437',
                               @SalesAmount = '14.00',
                               @SalesDate = '2022-07-06',
                               @Debug = 1;
GO

Notes on Debug Flag

You might say I can perform a SELECT statement on the temporary table while executing the individual statements. That is correct if you run the statements locally. However, if you or someone else executes the procedure, the temporary table will not exist after completion. Of course, you can use global temporary tables, but the debug flag is cleaner. If you would like to learn more about global temporary tables, please check out this tip from Sergey Gigoyan.

The same concept can be applied if you are a fan of using table variables. With table variables, SQL drops them after the batch executes, even when running them as ad hoc statements, which means you cannot go back and examine the results.

You can also add multiple debug flags. When you start adding debug flags, I am sure you will think of creative ways to incorporate them. Please let me know in the comments below if you currently use them.

Conclusion

In this tip, I demonstrated how to add a debug flag to a stored procedure. You can also add them to ad hoc scripts. We started by reviewing the reason for adding a debug flag in the first place. Most SQL developers want the ability to see data results at specific time points in their stored procedures. I recommend creating a local variable of the data type bit. I urge you to experiment and develop your unique ways of applying them. Once you do, please share them with others.

Next Steps

Another use case for a debug flag is returning row counts. Please explore this tip by Koen Verbeeck to learn more.
Would you like to make adding a debug flag even easier? Please consider creating a custom stored procedure template. Nai Biao Zhou covers all the details in this tip.
For a comprehensive overview of stored procedures, please look at this tutorial by Greg Robidoux.

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: 2022-08-02

Troubleshoot SQL Server Stored Procedure Execution Problems with Debug Flag