SQL Server Debugger

The SQL Server debugger, released with SQL Sever 2000, is an improved and easier-to-use tool, as compared to its earlier versions. Prior to SQL Server 2000, while the debugger existed, it required a relatively complicated setup procedure that most developers didn't bother to do. Now, this is a much more integrated solution, with the functionality being built directly into Query Analyzer.

Important

Visual Studio .NET also includes the ability to debug stored procedures. While we don't discuss Visual Studio .NET here, if you use it for your development you should check the documentation included with it for more details.

Before we start to explain the SQL Server Debugger, let's take a quick look at its user interface.

Finding the Debugger

As we mentioned, the debugger is integrated into Query Analyzer. The following screenshot shows a typical debugging session:

To start the debugging tool we need to locate a stored procedure by using the Object Browser of Query Analyzer. Then, right-click on the stored procedure and select Debug from the context menu; alternatively select Debug from the Window menu.

Important

If you receive the following error message, it means that the SQL Server service is currently running under the Local System security context. You need to change the service to run under a local machine user account or a domain user account before debugging will work. See SQL Server Books Online for more information on how to do this.

Using the Debugger

In this section, we will look at using the debugger for debugging a stored procedure. First, let's create the stored procedure to debug, which we will store in and run against the Northwind database.

     CREATE PROCEDURE DebugMePlease       @NoOfCustomers INT     AS     SET NOCOUNT ON     DECLARE @CustomerCount INT     SELECT @CustomerCount=Count(*)     FROM Customers     IF @CustomerCount>@NoOfCustomers     BEGIN       SELECT *       FROM Orders O       INNER JOIN [Order Deals] od ON o.Orderid=od.Orderid     END 

Now, let's try executing this procedure:

     EXEC DebugMePlease 10 

It will give us the following error message:

     Server: Msg 208, Level 16, State 1, Procedure DebugMePlease, Line 14     Invalid object name 'Order Deals'. 

As this was a simple stored procedure, we can quickly step through and identify the offending row. However, for the purposes of our discussion let's assume that this is a complicated process. So, instead of searching through it, we will debug it by using the debugger.

To do this we locate our stored procedure in the Object Browser (press F8 if it isn't visible) and then navigate into the Northwind database's stored procedures, until we find one called DebugMePlease. Right-click on it and select Debug.

You will be presented with a window, where we need to enter a value for our NoOfCustomers parameter. For now enter 10 and click Execute:

Within the Debug window we have the following choices:

Icon	Name	Description
	Go	Run the code within the debugger window either to the next breakpoint or the end of the procedure.
	Toggle Breakpoint	Place a breakpoint immediately preceding the current command. Clicking again removes a previously placed breakpoint.
	Remove All Breakpoints	Remove all breakpoints from the current debugger window.
	Step Into	Execute one statement at time. If the current statement is a call to another stored procedure, then that procedure will be opened within the debugger window for debugging.
	Step Over	Execute one statement at time. If the current statement is a call to another stored procedure, then that procedure will be executed as a single command without being opened within the debugger window.
	Step Out	If you are currently within a stored procedure that was called by a parent stored procedure, which is being debugged (because you stepped into it), this will execute the code to the end of the child procedure and step back out to the parent procedure.
	Run to Cursor	Executes all statements from the current position to the statement where the cursor is currently located.
	Restart	End the current execution and starts the stored procedure execution again from the beginning.
	Stop Debugging	Stops the current execution that is taking place within the debugger.
	Auto Rollback	When auto rollback is turned on, any changes made by statements that are executed during the debugging session are undone. When this is turned off, the changes made are retained; be careful while using this.

For our simple example, we will use the Step Into button to step through our code.

Important

If you hover the mouse pointer over the debugger buttons their equivalent key shortcuts will be displayed. It is often faster to step through code by using the shortcut key, for example pressing F11 will start the Step Into feature.

Notice that, as we step through the code, the current value of our local variables is displayed within the middle portion of the debugger window.

If we continue stepping through our simple procedure, we can quickly identify the T-SQL command that is the source of our error, which of course is this command:

       SELECT *       FROM Orders O       INNER JOIN [Order Deals] od ON o.Orderid=od.Orderid 

Now armed with this knowledge, we can fix our procedure by fixing the offending statement as shown below:

     ALTER PROCEDURE DebugMePlease       @NoOfCustomers INT     AS     SET NOCOUNT ON     DECLARE @CustomerCount INT     SELECT @CustomerCount=Count(*)     FROM Customers     IF @CustomerCount>@NoOfCustomers     BEGIN       SELECT *       FROM Orders O       INNER JOIN [Order Details] od ON o.Orderid=od.Orderid     END 

An now when we execute this procedure:

     EXEC DebugMePlease 10 

We get the desired output: