Debugging Stored Procedures with Query Analyzer

One of the great new tools available with SQL Server 2000 is the SQL Debugger built into Query Analyzer. You invoke the SQL Debugger by right-clicking on a stored procedure in the Object Browser and choosing the Debug option (see Figure 28.7).

Figure 28.7. Invoking the SQL Debugger in Query Analyzer.


If the stored procedure has input parameters, you will be prompted to enter values or provide a null value for the parameter (see Figure 28.8).

Figure 28.8. Providing input parameter values to the SQL Debugger in Query Analyzer.


The SQL debugger is similar to the debuggers that Microsoft provides with its other programming languages, such as Visual Basic or Visual C++ (see Figure 28.9).

Figure 28.9. The SQL Debugger interface.


The SQL Debugger has all the features you would expect to find in a debugger:

  • Ability to set and clear break points

  • Ability to step over/into next statement

  • Ability to run to end or next breakpoint

  • Ability to view contents of local and global variables

  • Ability to display messages/results generated within stored procedure

For a more detailed discussion on using the SQL Debugger, see Chapter 6.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features