Find and dissect errors with SQL Server's built-in bug hunter

by Baseline Inc. | Dec 30, 2002 8:00:00 AM

Takeaway: Debugging is a necessary evil of software development. Luckily, tools are available to make the process less painful. Microsoft provides the SQL Server Debug Interface to streamline SQL development.

Most developers proudly proclaim their code to be error-free, but deep down we all know that errors, whether logical or physical, can creep into the best project. With that said, one of the most time-consuming development tasks is debugging. We’ve all been there, tracking down an elusive bug. For this reason, most development toolkits include debugging utilities to aid in the bug hunt. Thankfully, Microsoft added such a tool to the SQL Server environment beginning with version 6.5. Let’s take a closer look at this handy tool and see how it may be used in daily programming chores.

Where is it?
The SQL Server Debug Interface (SDI) is accessible from the Query Analyzer client. Locate a stored procedure within the Query Analyzer Object Browser, as shown in Figure A, and right-click on the desired procedure name to display a pop-up menu, which offers the Debug option. The SQL Server administrator must give you appropriate access before you can work with the SDI, so check to ensure that you have this capability.

Figure A

The Query Analyzer offers a stored procedure Debug option.

Once you select the Debug option, the dialog box presented in Figure B appears. This allows you to provide any necessary parameters for the stored procedure. Once the parameters have been entered, click the OK button to begin a debugging session.

Figure B

You can enter stored procedure parameters for the debugging session.

For demonstration purposes, I have selected the SalesByCategory stored procedure located in the standard Northwind database. I supplied the parameters shown in Figure B (@CategoryName=Beverages and @OrdYear=1996) and started the debugging session. You may open only one debugging session at a time. Figure C shows the debugging window that allows you to work with code.

Figure C

Debugging interface

The debugging window contains five panes. The largest display is the main code pane; it contains the stored procedure code that is currently executing and contains numerous control buttons (I’ll cover these later). Immediately below the main display, the leftmost pane contains local variables, including parameters passed into the procedure and variables declared within the code. The middle pane contains global values, and the pane to the right is the call stack that contains a list of executing procedures. The call stack will expand if a procedure calls another procedure. The bottommost pane displays the running procedure’s output, whether this is errors or the expected results. Note that the example in Figure C contains the passed values entered in Figure B in the local variables.

Controlling execution
The value of a debugger is the freedom it affords you when executing questionable code. For example, you can set breakpoints throughout the stored procedure where execution stops until you tell it to proceed. In SDI, breakpoints and features are set with function keys (F9) and by selecting the buttons above the main code window. There are eleven buttons, which I’ve listed in Table A.
Table A

	Continue execution (F5)
	Set or disable a breakpoint (F9)
	Remove all breakpoints (CTRL + SHIFT + F9)
	Step into the code, execute one line (F11)
	Step over the code; executes the line but does not step into code called from a function or another stored procedure (F10)
	Step out of the code; completes execution of function or store procedure code; returning control to the calling code (SHIFT + F11)
	Run to the cursor (SHIFT + F10)
	Restart the code (SHIFT + CTRL + F5)
	Stop debugging (SHIFT + F5)
	Toggles auto rollback on/off
	Help (F1)

SDI's debugging commands

Take advantage of these buttons to assist with your debugging session. They allow you to execute the code and view the contents of variables. In addition, the local variables may be edited and replaced with a required value. Figure D shows the results of the sample parameters in the output pane.

Figure D

The stored procedure output

SDI strains the server where it is running, so restrict its usage to a development or test environment. You can’t edit code during debugging.

Clean up your code
The SQL Server Debug Interface is a powerful addition to any SQL developer’s toolset. Debugging is a necessity, whether you’re working with your own stored procedure or jumping into another developer’s handiwork. Take advantage of this tool in your next project.

12 comment(s)