ASA SQL User's Guide
  Debugging Logic in the Database
    Tutorial: Getting started with the debugger
      Lesson 2: Debug a stored procedure

Diagnose the bug

To diagnose the bug in the procedure, set breakpoints in the procedure and step through the code, watching the value of variables as the procedure is executed.

Here, you set a breakpoint at the first executable statement in the procedure.

To diagnose the bug

1. Change Sybase Central to Debug mode.

   From the Task menu, choose Debug. Sybase Central displays a Debugger Details pane at the bottom of the main window.

2. Set a breakpoint at the first executable statement in the procedure.

   The statement contains the following text:

   open cur_this_cust;

   Click to the left of this line in the vertical gray bar to set a breakpoint. The breakpoint appears as a red circle.

3. Execute the procedure again.

   1. In the left pane, right click the sp_customer_products procedure and choose Execute from Interactive SQL from the popup menu.

   2. A message box appears, asking if you want to debug the connection from Interactive SQL. Click Yes.

      Execution of the procedure stops at the breakpoint. A yellow arrow in the source code window indicates the current position, which is at the breakpoint.

4. Inspect variables.

   The Local variables window in the Debugger Details pane displays a list of variables in the procedure together with their current value and data type. The top_company, top_value, this_value, and this_company variables are all uninitialized and are therefore NULL.

5. Step through the code.

   Press F11 several times to step through the code, until you reach the following line:

     if this_value > top_value then

   As you step through the lines of the stored procedure, the value of the variables changes.

   When you are at the if statement, this_value is set to 3000 and top_value is still NULL.

6. Step into one more statement.

   Press F11 once more to see which branch the execution takes. The yellow arrow moves directly back to the label statement at the beginning of the loop, which contains the following text:

   customer loop: loop

   The if test did not return true. The test failed because a comparison of any value to NULL returns NULL. A value of NULL fails the test and the code inside the if...end if statement is not executed.

   At this point, you may realize that the problem is the fact that top_value is not initialized.