Debug SQL Server (Stored Procedures or Functions)

SQL statements can be difficult to diagnose and debug. SQL Server does not include any default way to debug and step through a stored procedure, but Visual Studio does. Using the Server Explorer, you can step through the execution of a stored procedure or function right inside of Visual Studio. The first step is to open the Server Explorer and create a data connection to your database.

You will then see the stored procedures and functions of your database listed in the Server Explorer.

From the Server Explorer, you can right-click on a stored procedure or function and you will see a menu item named Step Into Stored Procedure,

When you select Step Into Stored Procedure, you will see the Run Stored Procedure dialog,

After specifying the values for any parameters the stored procedure has, click the OK button. Visual Studio will now execute the stored procedure and open it in the document window, stopping in the first line of execution.

You can now step through the stored procedure as it executes. You can set breakpoints just as you would in normal codeā€”the only limitation is that you can specify only location and hit count breakpoints.

Because T-SQL is inherently different than .NET languages, the debugging experience is a little bit different. Here are some of the limitations with SQL debugging:

  • You can use only location and hit count breakpoints in T-SQL stored procedures and functions.

  • You cannot use Step Into to step from .NET managed code to T-SQL. You can set breakpoints in the stored procedure though, and the debugger will break when it comes across them.

  • You cannot use Break while a SQL statement is already running.

  • You can’t use the Set Next Statement function as you might in managed code.

Some other differences are the facts that you can’t use the memory or registers windows, as they just don’t apply to SQL. Unfortunately, SQL Print statements are not shown in the output window either.

You cannot run triggers directly, but you can set breakpoints in triggers, and if they are triggered, Visual Studio will break into their execution on those breakpoints.

Breakpoints – Tip of Week #19

Visual Studio offers a powerful debugger to aid with testing and troubleshooting your applications. One of the most common uses of a debugger is to set breakpoints, which are positions in the code that, when reached, cause the program execution to pause, allowing the developer to inspect the code and state of the program. When a breakpoint is reached and the application suspended, the application is in break mode. In break mode, you, the developer, can examine and change the values of the program variables.

The simplest way to add a breakpoint to a particular line of code is to click in the margin for that line of code. Or place the cursor on that line and press F9 to toggle breakpoint. You can also disable all breakpoints and delete all breakpoints from the debug menu.

brkmenu

The Breakpoints window provides a list of the current breakpoints and allows you to enable or disable breakpoints, delete breakpoints, add new breakpoints, and edit the properties of existing breakpoints. To display the Breakpoints window, go to the Debug menu’s Windows submenu and select the Breakpoints option. You can also display this window by pressing Ctrl-Alt-B (Debug.Breakpoints).

brkwndw

Break Only on Certain Conditions

Breakpoints in Visual Studio can be configured to cause the program to enter break mode only when a particular condition holds. To add a condition to a breakpoint, view the breakpoint’s properties by selecting the breakpoint from the Breakpoints window and clicking on the Properties icon. From any of the tabs, you will find a button titled Condition. Clicking on this will display the Breakpoint Condition dialog, where you can specify the condition to be watched. You can also right click on the breakpoint on the margin and select Condition,

brk

brkcond

Control How Often to Break on a Breakpoint

Breakpoints, by default, cause the program to enter break mode whenever they are hit and their condition, if any, is met. However, you can configure a breakpoint to enter break mode based on the breakpoint’s hit count. The hit count of a breakpoint is the number of times the breakpoint has been reached and the condition, if specified, has been met. Through the Breakpoint Properties dialog box, you can indicate when a breakpoint should cause the program to enter break mode based on its hit count value.

To configure this information, open up the properties for a breakpoint and click the Hit Count button Clicking on this button will display the Breakpoint Hit Count dialog.

brkcount