Debug Mode in SQL Server

Background

This article explains how to use debug mode in SQL Server. When we have a complex query, they can be tough to understand. In this type of problem we can handle using debug mode. In development we use the debug in one single day.

First Step

First of all we create a Stored Procedure for handling the while loop and insert into TestingTable.

My table looks as in:

Id               First               Second      Third

Make a Stored Procedure for inserting the value and check within debug mode.

ALTER Proc [dbo].[DebugModeInSql1] 1,2,3
(
   @First int=0,
   @second int=0,
   @third int=0,
   @Loop int=0

)
as
begin
   while(@Loop<20)
begin
   insert into testingTable values(@Loop,@Loop+1,@Loop+2,@Loop+3)
   set @Loop+=1
   end
end

How to debug mode activate

Select the procedure name, then press the ALT+F5 keys (combination).

 

First look at debug mode in the Stored Procedure. One question arises is how to select debug mode. First we select the Stored Procedure name as in the following images. On my system there is a popup window for the Firewall setting, I have no need for the network debug mode so please select the first option "Cancel Remote" debugging. I hope you have done that.   

 

 

                                       Fig. 1: First look of Debug Mode 

 

After the convert to normal to debug mode should be check debugging query mode like that image. 

 

 

                                       Fig. 2: Showing studio in debugging query.

 

After successfully converting to debug mode many windows will appear. Local and watch1 are very helpful for finding the problem. You can check that I passed the values 1, 2 and 3 on debugging Stored Procedure time. One more thing is that at the right now @loop has no value, in other words I have not passed a value for @loop by default value.

 

 

            Fig. 3: Locals window

Displays the value that is currently assigned to the local expression and the column is blank when no value has been assigned to the expression.  IN Locals windows is showing all your local and global variable values.

                        Fig. 4: Check @Loop value is continues increment.

 

                                          Fig. 5: Watch Window

 

This window helps to get a specific name value. Suppose we want to check the value of a local or global variable. Just enter the name in the Name column and press the Tab key, it will show the value in the Value column.

 

 

Fig. 6: SQL debug mode Activate

 

                                          Fig. 6: Watch window output.

 

Final words

If you have any doubt or query regarding this article then drop your comments in the comment box.