Debugging Tools and Techniques

Modern development environments contain sophisticated tools to help you debug your applications. The Transact-SQL Debugger in Visual Studio will help you to identify and fix problems in your code. However, even if your development environment does not support the Transact-SQL Debugger (if you do not have Visual Studio 2005), there are techniques you can employ to achieve the same results. I will discuss these techniques in "Poor Man's Debugger" later in this chapter.

Transact-SQL Debugger in Visual Studio 2005

This section demonstrates the use of the Transact-SQL Debugger from Visual Studio 2005. The major difference between debugging stored procedures and debugging within other programming languages is that you do not need to run the application to debug a single procedure.

The process for starting the debugger is to point to the stored procedure and invoke the Step Into Stored Procedure command:

1. Open Visual Studio.

2. Select View | Server Explorer.

3. In Server Explorer, right-click to open the context-sensitive menu and choose Add Connection.

4. In the Choose Data Source window, select Microsoft SQL Server and verify that .NET Data Provider for SQL Server is also selected. Click Continue.

5. In the Add Connection window, specify server name, authentication, and database name (as usual, use AssetS).

6. Test the connection and if everything is working, click OK to close each window.

7. Server Explorer now contains a node showing the connection that you have described. It functions like nodes in the Object Browser of Management Studio. Expand the nodes until you find Stored Procedures.

8. Right-click dbo.ap_InventoryProperties_Get_TempTblOuter to display the context-sensitive menu.

9. Choose Step Into Stored Procedure (instead of the usual Execute) and the program will initiate the debugging session.

10. Fill in the input parameters in the Run Stored Procedure window:

    

11. When you click OK, the program will display a window with the stored procedure in Debugging mode (see Figure 16-1).

Figure 16-1: A stored procedure in Debugging mode

The Transact-SQL Debugger opens the source code of the procedure and pauses on the first executable statement. A small yellow arrow on the left border marks the position of the statement to be executed next.

Debug Windows

The commands in the Debug menu become enabled, as do several more docked windows displayed as tabbed panes, described here, that enable you to examine the state of the environment.

• Locals window Allows you to scroll through the local variables and parameters of the stored procedure and to see its current contents and data type:

  

  As the stored procedure's code is executed, the values of variables change. To help you follow the execution, the Transact-SQL Debugger colors the values of variables that were changed in the previous statement. The Locals window allows you to change values of variables interactively during execution of the code (through the context-sensitive menu for the Value column).

• Watch window Has a similar function to the Locals window. You can type, or drag from the code, a Transact-SQL expression to be evaluated in this window. This feature is useful when you want to investigate the values of expressions in If, While, Case, and other similar statements. It is possible to work with up to four Watch windows.

• Output window Displays result sets returned by the Select statement and messages sent from the Print statement (see Figure 16-1).

• Breakpoints window Allows the user to define code locations (lines) and conditions for pausing execution. However, you can also associate it with some additional conditions. For example, you may want to create a breakpoint that pauses execution when a specified location is reached a specified number of times:

  

  These panes have more than four tabs, but only these mean anything for the Transact-SQL Debugger. The other tabs are used to debug client applications.

Breakpoints

Breakpoints are markers in code that serve to stop execution when certain conditions are met. In the Transact-SQL Debugger, you can use only a very limited set of conditions. Typically, you can only specify a condition in which the execution has reached the position of the breakpoint or in which a breakpoint has been reached a selected number of times. In other .NET languages such as C# and Visual Basic, the condition can apply when a variable changes value, when a Boolean expression is true, or even when execution starts a specified process or thread.

Breakpoints can be set three ways:

• Using the toolbar in the Breakpoints window

• Clicking the left edge of the code window

• Pressing F9 when the cursor is on the line in which you want to place a breakpoint

Visual Studio marks the breakpoint with a big red dot at the beginning of the line. If the dot contains a plus (+), the breakpoint contains additional criteria.

The toolbar and the Debug menu can contain additional commands for managing breakpoints. You can temporarily enable or disable all breakpoints and permanently remove them. A single breakpoint can be removed by clicking the dot or by pressing F9 when the cursor is on the line containing the breakpoint.

Stepping Through Execution

The majority of commands available on the Debug menu target execution control. Most of the time, you will use the Step Into or Step Over command to step through a stored procedure. These commands execute one Transact-SQL statement at a time. The difference between them is in the way they behave when they encounter a nested stored procedure:

• Step Into (F11) Opens the code of the nested stored procedure and lets you step through it.

• Step Over (F10) The nested stored procedure is treated as any other Transact-SQL statement and is executed in a single step.

• Step Out (SHIFT-F11) Enables you to execute the rest of the nested stored procedures without pause and halts only when the stored procedure is completed in the calling stored procedure.

• Run To Cursor (CTRL-F10) Enables you to position the cursor somewhere in the code and to execute everything to that point in a single step. In essence, this command lets you set a temporary breakpoint.

• Continue (FS) Resumes execution of the code until the (original entry) procedure is completed or until the next breakpoint is reached.

• Stop Debugging (SHIFT-FS) Discontinues execution.

Note 

Visual Studio menus can be customized to contain different sets of options. If your configuration does not contain options that are mentioned in this section, go to Tools | Customize, open the Commands tab, click the Debug category, and drag commands from the list to the Debug menu. Additionally, you can access these options using keyboard shortcuts even when the commands are not on the menu.

One of my favorite features in the Visual Studio debugger is the ability to continue execution from the position of the cursor. Unfortunately, due to the architecture of the Transact-SQL Debugger, the Set Next Step command is not available. Another cool feature that was originally available in Visual Basic and then missing in early versions of Visual Studio .NET was the ability to modify code on the fly (during debugging).