In Praise Of Recursion

I'd like to say the code posted last week solved the problem it was intended to solve, but alas, that was not the case. The job of resetting the Alerts criteria for a target database is a lot more complex than replacing a row in a table; it includes

• checking to see if each setting has actually changed for each different alert (all 29 of them),

• and if it has,

• and if that alert is currently in effect,

• then that alert must be cancelled,

• and that in turn might require an "All Clear" email to be sent

• as well as rows to be inserted in other tables.

None of that was accomplished by the cute little FOR INSERT SELECT * loop posted earlier.

The good news is, all the code to perform those tasks for a single target database already existed inside the stored procedure being modified... all it needed was

A DIFFERENT Cute Little FOR Loop

Here's the actual solution; a lot of code's been removed for clarity (about 6500 lines worth) but nothing else has been changed:

CREATE PROCEDURE rroad_monitor_options (
   IN @submit_form         VARCHAR ( 100 ),
   IN @sampling_id         UNSIGNED INTEGER,
   IN @display_html_page   VARCHAR ( 1 ) )
   RESULT ( html_output LONG VARCHAR )
BEGIN

...

IF @sampling_id = 0 THEN

   CASE @submit_form

      ...

      WHEN 'Force Default Settings On All Targets' THEN

         -- Perform 'Restore Default Settings' tasks on each target row.

         FOR f_alerts_criteria AS c_alerts_criteria INSENSITIVE CURSOR FOR
         SELECT rroad_alerts_criteria.sampling_id AS @target_sampling_id
           FROM rroad_alerts_criteria
          WHERE rroad_alerts_criteria.criteria_set_type = 'Target Specific'
          ORDER BY rroad_alerts_criteria.sampling_id
         FOR READ ONLY
         DO

            -- Recursively call this procedure.

            CALL rroad_monitor_options ( 
               'Restore Default Settings',
               @target_sampling_id,
               'N' );                       -- @display_html_page

         END FOR;

         ...

   END CASE;

ELSE -- @sampling_id > 0

   CASE @submit_form

      ...

      WHEN 'Restore Default Settings' THEN

         -- existing code to reset the alerts criteria for a single target database

      ...

• The CREATE PROCEDURE on lines 1 through 5 is normally called from an HTML service. The first parameter @submit_form tells the procedure what button was pressed; in this case, the existing 'Restore Default Settings' button versus the new 'Force Default Settings On All Targets'.

• The second parameter @sampling_id is 0 for the row containing default values, and 1, 2, 3 ... for rows representing actual target databases being monitored by Foxhound.

• The third parameter @display_html_page is 'Y' when the procedure is called from the HTML service and 'N' when it is being called elsewhere.

• The WHEN on lines 16 through 35 handles the 'Force Default Settings On All Targets' button press. The FOR loop steps through each target database with each primary key being placed in @target_sampling_id.

• The recursive CALL on lines 30 through 33 is the heart of this solution. The three parameter values are set up to pretend the user has pressed the 'Restore Default Settings' on a single target database, with @display_html_page set to 'N' because nothing is being sent back to the browser.

• The WHEN starting on line 47 marks the begining of the existing code which handles a 'Restore Default Settings' button press. This is the code invoked by the earlier recursive CALL.

To tell the truth, I can't remember the last time I coded a recursive call in the real world, if I ever have... and it probably won't happen again any time soon.

Which is too bad, because when the technique works, it works really well. And SQL Anywhere fully supports recursion and all the stacking and unstacking it requires, no fuss, no muss.

Question: What about performance?

Answer: 100 targets in less than two seconds, so nobody's complaining.