Debugging T-SQL Code in SQL Server Management Studio

By: Sergey Gigoyan | Comments (2) | Related: > SQL Server Management Studio

Problem

We are continuing to introduce SQL Server Management Studio (SSMS) common tips and in this article we are going to explore one of the most useful tools for developers – debugging. Sometimes, it is necessary to investigate problematic code and find out why it fails. This can cause a mess for developers especially when code is written by other developers who do not follow coding best practices, so the code is large and not well-organized. Fortunately there is a debugging feature in SSMS, which makes the above mentioned task much easier.

Solution

In this tip we are going to introduce SQL Server Management Studio debugging features and demonstrate how to do this with examples.

Starting Debugger in SSMS

Let’s introduce how the debugger works in an example.

Suppose we have found that our T-SQL code returns incorrect results, so we need to find the problem in the code and fix it. First of all, we need a test environment. Run the code below, to create sample database with its tables.

USE master
GO
 
CREATE DATABASE TestDB
GO
 
USE TestDB
GO
 
CREATE TABLE UserList
(
UserID INT IDENTITY(1,1),
UserName NVARCHAR(40) NOT NULL,
CONSTRAINT PK_UserList_UserID PRIMARY KEY CLUSTERED (UserID)
)
GO
 
CREATE UNIQUE INDEX UIX_UserList_UserName ON UserList(UserName)
GO
 
CREATE TABLE AdvancedUserList
(
UserID INT,
UserRank INT,
CONSTRAINT PK_AdvancedUserList_UserID PRIMARY KEY CLUSTERED (UserID),
CONSTRAINT CK_UserRank CHECK (UserRank IN (100, 200, 300)),
CONSTRAINT FK_AdvancedUserList_UserList FOREIGN KEY (UserID) REFERENCES UserList (UserID)
)
GO

We have a UserList table in the TestDB database and AdvancedUserList table. Advanced users are a subcategory of users from the UserList table, so the AdvancedUserList table refers to the UserList table by the UserID column. The following code creates a procedure that adds advanced users.

USE TestDB
GO
 
CREATE PROCEDURE uspAddAdvancedUser
@pUserID INT,
@pUserRank INT =100,
@pResCode INT OUTPUT ----0-OK, 1-ERROR
AS
BEGIN
 
    SET NOCOUNT ON
 
DECLARE @trancount BIT = 0
 
    BEGIN TRY

IF @@TRANCOUNT=0
BEGIN
BEGIN TRANSACTION
SET @trancount=1
END
 
INSERT INTO AdvancedUserList(UserID, UserRank)
VALUES  (@pUserID, @pUserRank)
 
IF @trancount=1
COMMIT
 
SET @pResCode=0
 
    END TRY
    BEGIN CATCH

IF @trancount=1
ROLLBACK
 
SET @pResCode=1
 
        SELECT ERROR_MESSAGE()
 
    END CATCH
 
END

The procedure below adds users. Moreover, if @pIsAdvancedUser parameter is passed as 1, it makes the added user as an advanced user by adding a corresponding record into the AdvancedUserList table using the uspAddAdvancedUser procedure.

USE TestDB
GO
 
CREATE PROCEDURE uspAddUser
@pUserName NVARCHAR(40),
@pIsAdvancedUser BIT = 0,
@pResCode INT OUTPUT --0-OK, 1-ERROR
AS
BEGIN
 
    SET NOCOUNT ON
 
DECLARE @UserID INT
 
    BEGIN TRY
 
        BEGIN TRANSACTION
 
        INSERT INTO UserList(UserName)
        VALUES  (@pUserName)
 
SET @UserID=SCOPE_IDENTITY()
 
IF(@pIsAdvancedUser=1)
EXEC uspAddAdvancedUser
@pUserID = @UserID,
@pUserRank = 500,
@pResCode = @pResCode OUTPUT
 
IF @pResCode=1
            RAISERROR('uspAddAdvancedUser failed',16,1)
 
IF(@pResCode=0)
COMMIT
ELSE
BEGIN
IF @@TRANCOUNT>0
ROLLBACK
 
SET @pResCode=1
END
 
    END TRY
    BEGIN CATCH
 
        IF @@TRANCOUNT>0
            ROLLBACK
 
SET @pResCode=1
 
        SELECT ERROR_MESSAGE()
 
    END CATCH
 
END

Now let’s call uspAddUser procedure to add a user (not an advanced user).

USE [TestDB]
GO
 
DECLARE@pResCode int
 
EXEC uspAddUser
@pUserName = 'Tom',
@pIsAdvancedUser = 0,
@pResCode = @pResCode OUTPUT
 
SELECT@pResCode as N'@pResCode'
 
 
SELECT * FROM dbo.UserList

We will see that nothing has been added and the procedure’s respond code is 1 that means that an error occurred.

Figuring Out the Problem with the Debugger

Now it is time to investigate the problem using the debugger. For debugging our current procedure we need to be in query window where it is called and start the debugger. To start the debugger we can use the “Debug” menu and choose “Start Debugging” or press the combination of Alt+F5 keys.

By pressing the F10 key we can step over code and with F11 we can step into code. Actually, if we are going to debug the code in the current query window, we can start the debugging just by pressing F10 (or F11 in we are going to enter into the code). In our example we should press F11 when we reach the line where the stored procedure is executed to enter into its code and investigate it row by row.

After pressing F11 the code for procedure uspAddUser appears.

By pressing F10 we are going down its code. We will find that after the insertion into the “UserList” table, the value of @pResCode parameter is not set to 0.

So, instead of being committed, the transaction is rolled back.

Now we can stop debugging, because we have already found the problem. We can stop by pressing “Stop Debugging” from the “Debug” menu.

Fixing Code and Testing Again

Using debugging we have found the mistake in our code and now we will fix it by adding a piece of code which sets @pResCode to 0 after a successful insertion.

USE TestDB
GO
 
ALTER PROCEDURE [dbo].[uspAddUser]
@pUserName NVARCHAR(40),
@pIsAdvancedUser BIT = 0,
@pResCode INT OUTPUT --0-OK, 1-ERROR
AS
BEGIN
 
    SET NOCOUNT ON
 
DECLARE @UserID INT
DECLARE @trnCount INT = @@TRANCOUNT
 
    BEGIN TRY
 
        BEGIN TRANSACTION
 
        INSERT INTO UserList(UserName)
        VALUES  (@pUserName)
 
SET @UserID=SCOPE_IDENTITY()
 
IF(@pIsAdvancedUser=1)
EXEC uspAddAdvancedUser
@pUserID = @UserID,
@pUserRank = 500,
@pResCode = @pResCode OUTPUT
ELSE
SET @pResCode=0

IF @pResCode=1
            RAISERROR('uspAddAdvancedUser failed',16,1)
 
IF(@pResCode=0)
COMMIT
ELSE
BEGIN
SET @pResCode=1
IF @@TRANCOUNT>0
ROLLBACK
END
 
    END TRY
    BEGIN CATCH
 
        IF @@TRANCOUNT>0
            ROLLBACK
 
SET @pResCode=1
 
        SELECT ERROR_MESSAGE()
 
    END CATCH
 
END

When we run the procedure again, we will see that it will successfully complete and one row is added to the “UserList” table.

Testing a Different Scenario

Now we are going to add another user and make it an advanced user. So, we are passing 1 as a @pIsAdvancedUser parameter.

USE [TestDB]
GO
 
DECLARE@pResCode int
 
EXEC uspAddUser
@pUserName = 'John',
@pIsAdvancedUser = 1,
@pResCode = @pResCode OUTPUT
 
SELECT@pResCode as N'@pResCode'
 
SELECT * FROM dbo.UserList

We will see that execution of this stored procedure fails.

As we can guess, the problem is in the “uspAddAdvancedUser” procedure, so at this time we will enter into this procedure code when debugging.

We will start debugging the nested stored procedure code by pressing F11 when we reach the line where it is called. After that, the code of the inner procedure will be opened.

Now it is visible that insert statement failed in the inner procedure, because there is check constraint on “AdvancedUserList” table and the value 500 cannot be set as a “UserRank”.

In the code below, our corrected outer procedure, that passes 100 instead of 500 to inner procedure:

USE TestDB
GO
 
ALTER PROCEDURE [dbo].[uspAddUser]
@pUserName NVARCHAR(40),
@pIsAdvancedUser BIT = 0,
@pResCode INT OUTPUT ----0-OK, 1-ERROR
AS
BEGIN
 
    SET NOCOUNT ON
 
DECLARE @UserID INT
 
    BEGIN TRY
 
        BEGIN TRANSACTION
 
        INSERT INTO UserList(UserName)
        VALUES  (@pUserName)
 
SET @UserID=SCOPE_IDENTITY()
 
IF(@pIsAdvancedUser=1)
EXEC uspAddAdvancedUser
@pUserID = @UserID,
@pUserRank = 100,
@pResCode = @pResCode OUTPUT
ELSE
SET @pResCode=0
 
IF @pResCode=1
            RAISERROR('uspAddAdvancedUser failed',16,1)
 
IF(@pResCode=0)
COMMIT
ELSE
BEGIN
SET @pResCode=1
IF @@TRANCOUNT>0
ROLLBACK
END
 
    END TRY
    BEGIN CATCH
 
        IF @@TRANCOUNT>0
            ROLLBACK
 
SET @pResCode=1
 
        SELECT ERROR_MESSAGE()
 
    END CATCH
 
END

After altering the procedure, the previous execution will work fine and the new row will be inserted in both tables.

USE [TestDB]
GO
 
DECLARE@pResCode int
 
EXEC uspAddUser
@pUserName = 'John',
@pIsAdvancedUser = 1,
@pResCode = @pResCode OUTPUT
 
SELECT@pResCode as N'@pResCode'
 
SELECT * FROM dbo.UserList
SELECT * FROM dbo.AdvancedUserList

Breakpoints

In addition, you can make your debugging process more flexible by using breakpoints. To add a breakpoint you can right click a line where the breakpoint is needed and select Insert Breakpoint.

You also can set a condition for a breakpoint.

Things to Note

It is important to understand that debugging code execution is also executing the code, therefore if your code completes successfully and you do not interrupt your debugging all changes will be saved permanently in the database.

So, if you want just debugging and do not want to execute anything in your code, before debugging you can include your code a transaction that will be rolled back at the end as follows:

USE [TestDB]
GO
 
DECLARE@pResCode int
 
BEGIN TRANSACTION
 
EXEC uspAddUser
@pUserName = 'Bob',
@pIsAdvancedUser = 0,
@pResCode = @pResCode OUTPUT
 
SELECT@pResCode as N'@pResCode'
 
SELECT * FROM dbo.UserList
 
ROLLBACK
 
SELECT * FROM dbo.UserList

Conclusion

In conclusion, the SSMS debugger is a powerful tool for investigating T-SQL code in detail. It facilitates a developer’s job to help find and correct mistakes in code and understand the code more easily.

Next Steps

Read the following materials for more information:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

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

View all my tips

Thursday, November 18, 2021 - 11:37:29 AM - SimpleSQLTutorials	Back To Top (89463)
An interesting thing to know is that Microsoft deprecated the debugging feature in SSMS in version 18.0, which was released in 2018. To debug SQL code in a newer version, you need to use the SQL Server Data Tools in Visual Studio.