Debugging Nested Stored Procedures

A stored procedure may call additional procedures as they execute, which can make debugging complicated. The Development Center Debugger has been designed to simplify this task. The parent stored procedure in which the debugger is initially started must be built in debug mode. When another procedure is called, at each nested level it is checked to see if it is also in debug mode. If the nested procedure has not been built in debug mode, it will be executed normally until it completes. If the nested procedure is also in debug mode, an edit window will be opened for it, and the debugger will begin to step through it. Consider the example shown in Table D.7.

Table D.7. Example of Debugging a Nested Stored Procedure

Procedure	Debug Mode	Procedure Called
Proc1	Debug	Proc2
Proc2	Debug	Proc3
Proc3	Normal	Proc4
Proc4	Debug

Table D.7 represents a series of stored procedure calls: Proc1 calls Proc2 which calls Proc3 which calls Proc4. All are built-in debug mode except for Proc3. A developer initially debugs Proc1 by stepping through it. The debugger will execute the procedure one line at a time until the call to Proc2 is reached. An edit window for Proc2 will then be opened, and it will be stepped through because was built in debug mode. The debugger will stop when the call to Proc3 is reached and will run the procedure normally. The debugger will renew stepping through the code when the call by Proc3 to Proc4 is reached. Upon the completion of Proc4, the debugger will return to running Proc3 normally without any debugging options. At the completion of Proc3, the debugger will resume stepping through the procedure in Proc2's edit window. Upon completion of Proc2, the debugger will return to the original calling Proc1. Debugging will finish once Proc1 completes.

More complex debugging tasks can also be accomplished by adding breakpoints in nested procedures, as shown in Table D.8. The initial procedure will run normally until the breakpoint is reached in a nested procedure. The nested procedure with the breakpoint must be built in debug mode to allow the procedure to be stepped through once the breakpoint is reached. The nested break points can be reached by using the Step to Cursor icon. If multiple breakpoints are embedded in the procedures, then clicking on the icon again will cause the program to execute until the next breakpoint is reached.

Table D.8. Example of Debugging a Nested Stored Procedure Using Breakpoints

Procedure	DebugMode	Procedure Called
ProcA	Debug	ProcB
ProcB	Normal	ProcC
ProcC	Debug	(with breakpoint)

In Table D.8, procedure ProcA is run in debug mode. The Step to Cursor icon is then clicked. ProcA makes a call to ProcB, which will be executed normally, and the code will not be debugged. The execution will continue until ProcB calls ProcC and the breakpoint in ProcC is reached. You can then step through ProcC starting from the breakpoint. Upon completion of ProcC, the debugger will return to executing ProcB normally until its completion. Finally, ProcA will be stepped through until it is completed.

Combining the mixture of debugged and non-debugged procedures with breakpoints allows problems that may be buried many levels down in a nested procedure to be found and tested easily. The procedures can be tested for correctness by ensuring that the top-level procedure is performing correctly until the first nested procedure call. A breakpoint can then be added just after the call so that earlier tested code does not have to be stepped through. As each layer of procedure is fully tested, the breakpoint can be moved or the procedure can be removed from being debugged completely by building it in normal mode. Stored procedures that call dozens of other procedures or are thousands of lines long can then be tested on a component or sectional basis without having to re-test code.