SQL Debugger in dbForge Studio for SQL Server

SQL Debugger is an advantageous feature of dbForge Studio for SQL Server allowing you to troubleshoot Transact-SQL scripts, stored procedures, triggers, and functions quickly and easily. Automated debugging capabilities take away the necessity to comb through the scripts in an attempt to find bottlenecks. Stop compromising on code quality with T-SQL Debugger incorporated into Studio for SQL Server.

T-SQL Debugger functionality

With T-SQL Debugger, you can benefit from the support for step-by-step execution, call stack browsing, breakpoints, and watches. Debugging your code right in the IDE, you save time and effort, avoid performance issues, and minimize downtime.

T-SQL Debugger integrated into dbForge Studio for SQL Server allows debugging:

  • Stored Procedures
  • Triggers
  • Stored Functions
  • SQL scripts
  • Complex SQL queries

SQL Debugger permits going through the code line by line, stepping in and out routines, procedures, and functions. The tool supports single-stepping, pausing, and setting breakpoints within the code, making it easy to evaluate variables and analyze the changes in their values.

T-SQL Debugger built into dbForge Studio for SQL Server

Requirements to start debugging

To start debugging, make sure that the following requirements are met:

  • Server-side and client-side components of T-SQL Debugger are installed
  • Debugging firewall exceptions on the client and server sides are enabled
  • A Database Engine SQL document is connected using the Windows Authentication or SQL Server Authentication login that is a member of the sysadmin fixed server role
  • A Database Engine SQL document is connected to an instance of the Database Engine from SQL Server 2005 Service Pack 2 (SP2) or later
  • The database you're working with is not in the single-user mode

How to debug a trigger in SQL Server

To ensure that the trigger is running and is called correctly, you might need to debug it. Debugging triggers can be tortuous as they perform complex operations silently.

If the trigger is fired by a stored procedure, you can debug both database objects as a whole, stepping into or over a trigger.

Let's illustrate how to debug a trigger in SQL Server step by step.

In Database Explorer, double-click the trigger that you want to debug. In the SQL document that opens, you need to set a breakpoint. To do that, right-click the line of the code where you want to set a breakpoint and select Insert Breakpoint. The lines with the breakpoint will be highlighted in red.

Debug SQL Server - Insert breakpoints for the trigger

In Database Explorer, double-click the procedure to open it. In the SQL document that opens, set a breakpoint for the stored procedure that will fire that trigger. The breakpoints that you inserted in the trigger and stored procedure will be added to the Breakpoints pane where you can delete, enable or disable all breakpoints or the selected ones, or go to the source code.

Debug SQL Server - Insert breakpoints for the procedure

On the Debug menu, click Start to launch debugging. In the Edit Parameters dialog that opens, enter the values of input variables and then click OK. The variables LowerRange and UpperRange define the range of values.

Enter the values of input variables

To step through the code, click Step Into or press F11 on the Debug menu or toolbar. Alternatively, you can press CTRL+F5 to move directly to the breakpoint. When you press F11 again, you will step into the trigger.

After debugging is complete, the Output pane automatically opens where you can view and analyze the execution result of the debugging process.

View the execution result of trigger debugging

Debugging a stored procedure

T-SQL Debugger provides all the necessary facilities for you to interactively debug stored procedures right in the IDE using traditional debugging operations such as:

  • Setting breakpoints
  • Evaluating and editing variables
  • Examining the call stack
  • Stepping into, out, and over individual statements
  • Suspending the execution of your program, etc.

The Transact-SQL Debugger tool is well elaborated to suit the most demanding needs. The Call stack pane allows tracking calls to nested procedures. The Watches pane in its turn gives a possibility to evaluate SQL stored procedure variables and parameters.

SQL Server debugging - debug SQL stored procedure

Debugging a stored function

Functions are generally not so easy to diagnose and debug. The Debugger tool that comes with dbForge Studio for SQL Server allows investigating the run-time behavior of your functions and spotting logic errors on-the-fly.

To debug a function, open the procedure calling that function and insert a breakpoint for the function you want to debug. Then, start debugging. Step through the code using the F11 key or Step Into, or press CTRL+F5 to move directly to the breakpoint. Press F11 or click Step Into to get inside the stored function. Clicking Step Over will continue stepping through the stored procedure.

SQL debug - Function debugging

Debugging a SQL script

Debugging large and complex SQL queries can be a daunting task. The script execution causes errors but they do not directly point at the problematic places in the script. Here T-SQL Debugger built into dbForge Studio for SQL Server comes to the aid.

To debug a SQL script, open it in the SQL code editor and start debugging. Then, set breakpoints to the statements if required. If a statement is invalid, an error message appears in the error list, though the debugging process continues. You can suspend the debugging of a query by selecting the Stop Debugging command from the Debug menu. You can also use different step commands to trace a SQL script.

The Call Stack and the Watches panes are also available for this debugging task.

Debug SQL query in SQL server

Executing Step into, Step out, and Step debugging Commands

For user convenience, the Transact-SQL Debugger functionality of dbForge Studio for SQL Server can boast a number of control techniques allowing you to manage the debugging of stored database objects:

Step Into: If the line of the code contains a function call, the debugger will enter the called function and continue line-by-line debugging there.

Step Over: In case the line contains a function call, the function will be executed and the result returned without debugging each line.

Step Out: Use the Step Out command, if you are inside a function and want to return to the preceding stack frame.

The Continue, Stop debugging, and Restart commands allow you to directly control the flow of the debugging process.

MS SQL debug stored procedure - debugging execution control

Call Stack browsing

To keep track of the stored procedures or function invocations, you can use the Call Stack pane available only in the debug layout. The pane can also be used to find and analyze logic errors when executing the queries. The Call Stack pane represents a stack frame of functions, stored procedures, and triggers are being called in the order displaying the most recent function or procedure on top of the pane.

In the Call Stack pane, you can:

  • Navigate between calls
  • Identify the stack frame where the T-SQL debugger is currently located (indicated by the yellow arrow)
  • Identify the parent code which has called the trigger (indicated by the green arrow)
  • Detect the line where the debugger stopped the execution
  • Switch to the source code
Call Stack pane in dbForge Studio for SQL Server

Working with breakpoints

During a debugging session, the breakpoints can be used to suspend or pause the code execution in order to deeply analyze the code and evaluate variables. The breakpoints you place in the code line during the debug mode are displayed in the Breakpoints pane. After the code breaks, you can launch the debugging process with the Step Into, Step Over, Step Out or Run to continue debugging commands.

In the Debug > Breakpoints pane, you can manage breakpoints in the following way:

  • View a list of breakpoints along with its properties that are currently set in the code
  • Delete the selected breakpoint or all breakpoints
  • Enable or disable breakpoints
  • Switch to the source code
Working with Breakpoints in dbForge Studio for SQL Server

Analyzing variables in the script

While stepping through the code, you can add the watches (expressions) and evaluate or modify local variables. When the Transact-SQL debugger pauses the code execution, the expressions are displayed in the Watches pane that is available only in the debug layout.

The Watches pane enables you to view the following information:

  • Name: Variables and expressions run in the debugger
  • Value: Evaluation result of the expression specified in the Name column. The evaluation is based on the current stack frame selected in the Call Stack pane.
  • Type: Data type of the expression
Analyzing Variables in the Script while debugging with dbForge Studio for SQL Server