In addition to the debugger provided with SQL Server Query Analyzer, a T-SQL debugger is provided with the Visual Studio development suite.
If you don't find SQL Server Debugging in the Control Panel, Add/Remove Programs dialog box, you have to add the software. Run the Visual Studio setup program, choose Server Applications and Tools, Launch BackOffice Installation Wizard, Install, Custom, and install SQL Server Debugging.
You must have Visual Studio Version 6.0 to debug stored procedures. The T-SQL debugger in Visual Basic 5.0 or Visual Studio 97 does not support SQL Server 2000.
The enterprise edition of Visual Studio (Visual InterDev, Visual J++, and Visual C++) includes a debugger for the Visual Studio developer environment. The project you are working in needs to include a database project.
Open a stored procedure to open the context menu (by right-clicking within your code) and set breakpoints. When you choose Debug from the context, a dialog box asks for the parameters that the procedure expects. As you step through the code, you can inspect the values of variables in the Locals window.
The T-SQL debugger in Visual Basic is similar. Open your Visual Basic project, choose the Add-In Manager, and load the T-SQL debugger. Within the debugger, you define the connection attributes and choose which procedures you want to debug.