• Article

Stepping Through Transact-SQL Code

The Transact-SQL debugger enables you to control which Transact-SQL statements are run in a Database Engine Query Editor window. You can pause the debugger on individual statements and then view the state of the code elements at that point.

Breakpoints

A breakpoint signals the debugger to pause execution on a specific Transact-SQL statement. The act of setting a breakpoint on a statement is called toggling a breakpoint.You can toggle a breakpoint on a Transact-SQL statement by selecting the statement and performing one of the following actions:

  • Press F9.

  • On the Debug menu, click Toggle Breakpoint.

  • In the Query Editor window, click the gray bar to the left side of the Transact-SQL statement that you want.

To view and manage all the open breakpoints, you can use the Breakpoints window. The Breakpoints window lists information such as which line of code the breakpoint is located on. In the Breakpoints window, you can also delete, disable, and enable breakpoints. For more information about the Breakpoints window, see Breakpoints Window.

You can open the Breakpoints window in one of the following ways:

  • On the Debug menu, click Windows, and then click Breakpoints.

  • On the Debug toolbar, click the Breakpoints button.

  • Press CTRL+ALT+B.

You can temporarily disable a breakpoint. This prevents the breakpoint from pausing execution, but leaves the definition in place in case you want to reenable the breakpoint later.

The following table lists the various ways in which you can disable, reenable, and delete breakpoints.

Action

Procedure

Disable an individual breakpoint

  • In the Query Editor window, right-click the breakpoint, and then click Disable Breakpoint.

  • In the Breakpoints window, clear the check box to the left of the breakpoint.

Disable all breakpoints

  • On the Debug menu, click Disable All Breakpoints.

  • On the toolbar of the Breakpoints window, click the Disable All Breakpoints button.

Reenable an individual breakpoint

  • In the Query Editor window, right-click the breakpoint, and then click Enable Breakpoint.

  • In the Breakpoints window, click the check box to the left of the breakpoint.

Reenable all disabled breakpoints

  • On the Debug menu, click Enable All Breakpoints.

  • On the toolbar of the Breakpoints window, click the Enable All Breakpoints button.

Delete an individual breakpoint

  • In the Query Editor window, right-click the breakpoint, and then click Delete Breakpoint.

  • In the Breakpoints window, right-click the breakpoint, and then click Delete on the shortcut menu.

  • In the Breakpoints window, select the breakpoint, and then press DELETE.

Delete all breakpoints

  • On the Debug menu, cllick Delete All Breakpoints.

  • On the toolbar of the Breakpoints window, click the Delete All Breakpoints button.

Note

The Transact-SQL debugger does not support the Microsoft Visual Studio features of setting breakpoint conditions or hit counts.

Controlling Statement Execution

In the Transact-SQL debugger, you can specify the following options for executing from the current statement in Transact-SQL code:

  • Run to the next breakpoint.

  • Step into the next statement.

    If the next statement invokes a Transact-SQL stored procedure, function, or trigger, the debugger displays a new Query Editor window that contains the code of the module. The window is in debug mode, and execution pauses on the first statement in the module. You can then move through the module code, for example, by setting breakpoints or stepping through the code.

  • Step over the next statement.

    The next statement is executed. However, if the statement invokes a stored procedure, function, or trigger, the module code runs until it finishes, and the results are returned to the calling code. If you are sure there are no errors in a stored procedure, you can step over it. Execution pauses on the statement that follows the call to the stored procedure, function, or trigger.

  • Step out of a stored procedure, function, or trigger.

    Execution pauses on the statement that follows the call to the stored procedure, function, or trigger.

  • Run from the current location to the current location of the pointer, and ignore all breakpoints.

The following table lists the various ways in which you can control how statements execute in the Transact-SQL debugger.

Action

Procedure

Run all statements from the current statement to the next breakpoint

  • On the Debug menu, click Continue.

  • On the Debug toolbar, click the Continue button.

Step into the next statement or module

  • On the Debug menu, click Step Into.

  • On the Debug toolbar, click the Step Into button.

  • Press F11.

Step over the next statement or module

  • On the Debug menu, click Step Over.

  • On the Debug toolbar, click the Step Over button.

  • Press F10.

Step out of a module

  • On the Debug menu, click Step Out.

  • On the Debug toolbar, click the Step Out button.

  • Press SHIFT+F11.

Run to the current cursor location

  • Right-click in the Query Editor window, and then click Run To Cursor.

  • Press CTRL+F10.