How to run the stored procedure
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
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
|ABOUT THE AUTHOR: |
About the author: Joe Toscano is a SQL Server instructor, speaker and database consultant for Micro Endeavors Inc. He has worked extensively with SQL Server since version 6.5, as well as Sybase and Informix prior to that. Toscano's areas of expertise include Data Transformation Services (DTS), Integration Services (SSIS), Transact-SQL (T-SQL) programming, data warehousing, performance tuning, replication, high availability (mirroring), security, disaster recovery, upgrading, migration projects and lights-out DBA tasks.
Copyright 2006 TechTarget
This was first published in July 2006