Visual Studio provides us with many informative debug windows. I'll walk through our sample code to review several of these windows, starting with the Autos window.
The Autos Window displays variables used in the current statement. When the yellow arrow points to the select @CategoryName = @CategoryName + '%' line of code, notice the value of @CategoryName is the value prior to the statement actually being executed. In this case, in Figure 4, we have yet to append a percentage to this parameter.
Figure 4: Autos window
The Locals window displays the current local variables and parameters and allows you to change the values of these variables interactively during code execution. This window color codes variable values that were changed. In Figure 5 you can see that I changed "Bikes" to "Clothing," and to identify this Visual Studio, I made this new value red.
Figure 5: Locals window
The Watch 1 window allows you to type or drag from code the T-SQL Expressions to be evaluated in this window and see what the code actually evaluates. This may be useful if you want to investigate the values of expressions contained in conditional expressions, such as IF, WHILE or CASE. You can actually work with up to four watch windows.
The Output window displays the result set returned by select or print statements. In our example, we returned 35 matching "Clothing" rows (see Figure 6).
Figure 6: Output window
The Hovering Value window is not really a window, but it's a great feature worth pointing out. If you hover your cursor over your lines of code while in debug mode, you will see the values of variables associated with that particular line. This seems similar to the Windows application functionality in which you hover your cursor over a toolbar icon without clicking on it to get help.
The Breakpoints window displays your current breakpoints and allows you to add them. Breakpoints are user-defined code locations and/or conditions that pause execution, allowing the debugger to reflect, inspect and so on. You can add breakpoints by clicking to the left edge of the code window below, identified by the yellow arrow. Notice that the Breakpoints window in Figure 7 is actually divided into two windows. The lower window provides breakpoint information and tells us there is a breakpoint on line 20. The upper window of Figure 7 contains a code section. In this upper window we do see the red bubble to the left of line 20, which is Visual Studio's breakpoint indicator.
Figure 7: Breakpoints window
Conclusions
Visual Studio 2005 has easy-to-use graphical debugging tools for your T-SQL stored procedures. You may want to take advantage of this when unit testing your code. Since developers typically unit test code in the absence of all supporting cast procedures or functions, the Step Over option could really come in handy. Also, since you can easily change values of input parameters or local variables, you can force your program's flow down paths, which will exercise specific portions of code.
How to debug T-SQL stored procedures
Home: Introduction
Part 1: Sample stored procedure
Part 2: Where to start debugging the stored procedure
Part 3: How to step into or run the stored procedure
Part 4: Visual Studio debug windows
