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

 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

ABOUT THE AUTHOR:

Joe Toscano 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

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server Stored Procedures SQL Server Mailbag: CALs, witnesses and unwanted changes SQL Server Mailbag: Stored procedures, triggers and SSRS reports Top tips and tricks for SQL Server database development Top 10 SQL Server development tips of 2008 SQL Server trigger vs. stored procedure to receive data notification SQL Server errors, failures and other problems fixed from the trenches SQL Server and data manipulation in T-SQL How to use SQL Server 2008 hierarchyid data type SQL Server stored procedures tutorial: Write, tune and get examples Check SQL Server database and log file size with this stored procedure SQL/Transact SQL (T-SQL) Using DELETE and TRUNCATE TABLE statements to delete data in SQL Server SQL language crash course (just enough to be dangerous) Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Working with sparse columns in SQL Server 2008 Determining the source of full transaction logs in SQL Server New GROUP BY option provides better data control in SQL Server 2008 Using the OPENROWSET function in SQL Server Loading data files with SQL Server's BULK INSERT statement Importing and exporting bulk data with SQL Server's bcp utility SQL/Transact SQL (T-SQL) Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary library  (SearchSQLServer.com) trigger  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary