How to debug T-SQL stored procedures

These easy-to-use graphical debugging tools in Visual Studio 2005 will help simplify the process of unit testing your T-SQL code.

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 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.

This was first published in July 2006

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close