Contents

How to debug a stored procedure

The tutorial describes the steps to debug a stored procedure with the help of T-SQL Debugger built into dbForge SQL Complete. For demo purposes, we are going to use the HumanResources.uspUpdateEmployeePersonalInfo stored procedure from the AdventureWorks2019 database.

To start debugging a stored procedure:

1. In Object Explorer, right-click the procedure to debug and select Script Stored Procedure as > EXECUTE To > New Query Editor Window.

2. To start debugging, click Step Into or press F11 until a new SQL document with the CREATE statement for the procedure opens.

Note

The yellow arrow identifies the stack frame where the execution pointer is currently located.

You can view the value of the variable by hovering over the variable in the query. The quick info tip will be displayed. However, be sure that the Enable code completion feature is selected in SQLComplete > Options > General.

3. In the CREATE statement document, insert a breakpoint by using one of the following options:

• Right-click the line of the code where you want to set a breakpoint and select Insert Breakpoint.
• Place a cursor on the line of the code you want to break on, navigate to the Debug menu, and select Toggle Breakpoint or press F9.
• Click on the gray bar to the left of the code where you want to insert a breakpoint.

In the SQL query editor, the line with the breakpoint will be highlighted with the red color.

4. On the Debug toolbar, click Continue or press Alt+F5.

Once done, the Breakpoints pane opens where you can view breakpoints set in the code and manage them as follows:

• Delete the selected breakpoint by clicking Delete
• Delete all breakpoints by clicking Delete All Breakpoints
• Disable all breakpoints by clicking Disable All Breakpoints
• Switch to the source code by clicking Go to Source Code

5. Add the variable to the Watches pane. To do that, right-click the variable and then select Add Watch.

Note

The Watches pane displays variables, their values, and types (values of variables being tracked).

If you want to delete a watch, in the Watches pane, right-click the watch and select Delete Watch.

6. On the Debug toolbar, click Step Into or press F11 repeatedly to step through the procedure. While stepping through the procedure, the values of variables being tracked are changed in the Watches pane.

Note

If the stored procedure calls a function or a trigger, you may select Step Into or Step Over on the Debug toolbar.

7. To stop debugging, use one of the following options:

• On the Debug menu, select Stop Debugging or press Shift+F5.
• On the Debugger toolbar, click Stop Debugging.
• On the Debugger toolbar, click Step Over or press F10.

After you have debugged the stored procedure, you can execute it.

Note

You may ignore stepping into the function or trigger by clicking Step Over. In this case, you will continue stepping through the stored procedure.

If you stepped into the function or trigger, you can get back to the stored procedure code by clicking Step Out. After that, you will continue stepping through the stored procedure.

You may click the procedure in the Call Stack pane to get back to the parent code.

Video tutorial

Watch the video tutorial to learn how to debug a stored procedure in SSMS using SQL Complete.

Want to Find out More?

• Previous
• Next