How to debug T-SQL stored procedures

Continuing our series on how to tune and improve your Transact-SQL 2005 stored procedure code, this tip covers Visual Studio 2005 debugging capabilities.

I will execute -- or step into -- a sample T-SQL stored procedure and assign values to input parameters, inspect variable contents, follow the logical flow of the procedure during runtime, evaluate T-SQL expressions, view the procedure's output, set breakpoints and generally examine the state of the environment. (Future tips will continue along this same theme.) We will debug our procedure, not from Management Studio but from the Visual Studio 2005 development environment. I mention this because under SQL Server 2000 we are able to debug stored procedures using Query Analyzer. Perhaps debugging capabilities will be added to Management Studio in the future.

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

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. Digg This!     StumbleUpon     Del.icio.us    RELATED CONTENT Stored Procedures Check SQL Server database and log file size with this stored procedure Configure SQL Server Service Broker for sending stored procedure data Find size of SQL Server tables and other objects with stored procedure Stored procedure to monitor long-running jobs in SQL Server 2000 Make changes to SQL Server stored procedures with batch editing Stored procedure to find fragmented indexes in SQL Server Use table-valued parameters for SPs in SQL Server 2008 Examples of SQL Server stored procedures and parameters Top 10 SQL Server development questions FAQ: SQL Server stored procedure how-tos SQL Server stored procedures Check SQL Server database and log file size with this stored procedure SQL Server source code analysis and management adds database security SQL and SQL Server Tutorial and Reference Guide Configure SQL Server Service Broker for sending stored procedure data Find size of SQL Server tables and other objects with stored procedure Track changes to SQL Server 2000 and 2005 with one simple utility Troubleshoot SQL Server 2005 temporary table performance problems Use SQL Profiler to find long running stored procedures and commands Stored procedure to monitor long-running jobs in SQL Server 2000 Using BULK INSERT to insert rows from SQL Server dataset to table SQL/Transact SQL (T-SQL) How to use rank function in SQL Server 2005 Create a computed column in SQL Server using XML data Using the OUTPUT clause for practical SQL Server applications Create DDL table in SQL Server 2005 to audit DDL trigger activity SQL Server source code analysis and management adds database security SQL and SQL Server Tutorial and Reference Guide Retrieve XML data values with XQuery in SQL Server 2005 How to construct and use SQL OUTER JOINs optimally How to use the LEFT vs. RIGHT OUTER JOIN in SQL Using the FULL OUTER JOIN in SQL 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 DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.