How to debug a function

Last modified: March 2, 2023

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

To debug a Microsoft SQL Server function:

1. On the standard toolbar, click New Query and type the following EXEC statement:

EXECUTE dbo.ufnGetStock @ProductID = 0

2. To start debugging, click Step Into or press F11 until a new SQL document with the CREATE statement for the function 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.

In the Breakpoints pane that opens, 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 value, and type (values of variables being tracked). This option allows you to track the values of the variables while stepping through the code.

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

Note

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

Step through the function until you go back to the function, and continue to the end.

Note

You may get back to the function code by clicking Step Out. In this case, you will continue stepping through the function.

You may click the function in the Call Stack window to get back to the parent code.

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.