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:
