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.
