How to Debug Stored Procedures in SQL Server 2005

With Microsoft SQL Server 2000 it was possible to debug stored procedures from directly within Query Analyzer. With SQL Server 2005, however, this functionality was moved out of SQL Server Management Studio and into the Visual Studio IDE. All editions of SQL Server 2005 include debugging support (including the Express Edition). However, only the Team Systems and Professional editions of Visual Studio enable stored procedure debugging from within the IDE.
Let’s consider how to debug SQL Server 2005 stored procedures through the Visual Studio IDE.
For Microsoft SQL Server 2005, all database debugging occurs from within the Visual Studio IDE. Database objects like stored procedures, triggers, and User-Defined Functions (UDF) can be debugged. Visual Studio offers three ways to debug these database objects:

• Direct Database Debugging - from Visual Studio's Server Explorer, right-click on a database object and choose to step into the object. For example, when right-clicking on a stored procedure, the context menu includes a menu option titled "Step into Stored Procedure."

• Application Debugging - with application debugging you can set breakpoints within a database object. When the associated ASP.NET application is debugged and the database object invoked, Visual Studio's debugger pauses the application's execution as the breakpoint it hit, allowing us to step through the object's statements one at a time.

• Debugging from a SQL Server Project - Visual Studio offers a SQL Server Project type. This project can include both T-SQL and managed database objects and these objects can be debugged by debugging the SQL Server Project itself.
  For more information on these three types of SQL Server debugging, see Overview of T-SQL and CLR Debugging in SQL Server 2005.