By stepping into the procedure, you are essentially telling Visual Studio to start running the procedure line by line. Since our sample procedure does accept input parameters, you will see a Local Window that allows you to scroll through the local variables and parameters of the stored procedure. In Figure 2 under the Direction column header, you'll notice that Visual Studio identifies the output parameters for you, and the Value column is the only column in this window you can change. In this case, I've supplied Bike as a CategoryName value.
Figure 2: Output parameters
Visual Studio provides numerous windows in which to examine the state of the environment. The line of code waiting to be executed is identified by the yellow arrow in the screenshot in Figure 3.
Figure 3: Code to be executed
Most of the time, you will Step Into or Step Over commands in your stored procedures. The commands below apply to a single T-SQL line:
- Step Into (F11): Use to single step through your code. (Move the yellow arrow down one statement.)
- Step Over (F10): Useful if you have lines of code that perhaps modify data or call other procedures that you don't care about while debugging. For example, you may want to skip code that performs auditing.
- Step Out (SHIFT-F11): Execute the rest of the stored procedure without pause.
- Run to Cursor (CTRL-F10): Position the cursor to a point in your code and then hit CTRL-F10 to execute all code up to that point.
- Continue (F5): Resumes execution until completion or until the next breakpoint (more on breakpoints in a minute).
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
