Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Debugging T-SQL Code in SQL Server Management Studio


By:   |   Updated: 2017-12-08   |   Comments (1)   |   Related: More > 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
 
[email protected] int
 
EXEC uspAddUser
@pUserName = 'Tom',
@pIsAdvancedUser = 0,
@pResCode = @pResCode OUTPUT
 
[email protected] 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.

query results

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.

ssms debug menu

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.

debug code in ssms

After pressing F11 the code for procedure uspAddUser appears.

debug code in ssms

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.

debug code in ssms

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

debug code in ssms

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

ssms 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.

query results

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
 
[email protected] int
 
EXEC uspAddUser
@pUserName = 'John',
@pIsAdvancedUser = 1,
@pResCode = @pResCode OUTPUT
 
[email protected] as N'@pResCode'
 
SELECT * FROM dbo.UserList 

We will see that execution of this stored procedure fails.

query results

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

debug code in ssms

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.

debug code in ssms

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”.

debug code in ssms

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
 
[email protected] int
 
EXEC uspAddUser
@pUserName = 'John',
@pIsAdvancedUser = 1,
@pResCode = @pResCode OUTPUT
 
[email protected]pResCode as N'@pResCode'
 
SELECT * FROM dbo.UserList
SELECT * FROM dbo.AdvancedUserList 
query results

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.

breakpoints in ssms

You also can set a condition for a breakpoint.

breakpoint condition in ssms

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
 
[email protected] int
 
BEGIN TRANSACTION
 
EXEC uspAddUser
@pUserName = 'Bob',
@pIsAdvancedUser = 0,
@pResCode = @pResCode OUTPUT
 
[email protected] as N'@pResCode'
 
SELECT * FROM dbo.UserList
 
ROLLBACK
 
SELECT * FROM dbo.UserList 
query results

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:



Last Updated: 2017-12-08


get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan 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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, December 08, 2017 - 8:15:48 AM - DCT Back To Top

Can you confirm that you are debugging on your localhost and not connected to a remote server?  I believe debuggin only works on localhost. 

 


Learn more about SQL Server tools