Product Suggestion: Nested Internal Procedures

SQL Anywhere already has nested BEGIN blocks, complete with variable scope rules, local variables, even local EXCEPTION handling. What it needs is an extension to the BEGIN statement to define nested internal PROCEDURE blocks with CALL, RETURN and parameter passing.

A local variable defined at the front of a BEGIN block is created when the block is entered and destroyed when the block terminates. The scope of a local variable includes all the executable statements in the same BEGIN block, as well as all statements in any nested BEGIN block. - page 262, SQL Anywhere Studio 9 Developer's Guide

Before...

Here's an excerpt from a large stored procedure that mimics "calls" to local sections of code by using Y/N variables and IF statements. All the code is local to one single CREATE PROCEDURE because of the large number of local variables (over 200, not shown) that are shared across the different sections; using a separate CREATE PROCEDURE for each section of code would require thousands of lines of additional code to implement parameter passing.

CREATE PROCEDURE rroad_monitor_options (
   ...
BEGIN
   DECLARE @do_task_load_criteria_into_@variables                     VARCHAR ( 1 );
   DECLARE @do_task_copy_inputable_@variables_into_@old_variables     VARCHAR ( 1 );
   DECLARE @do_task_load_factory_settings_into_inputable_@variables   VARCHAR ( 1 );
   DECLARE @do_task_cancel_alerts_as_required                         VARCHAR ( 1 );
   DECLARE @do_task_replace_entire_target_row_with_@variables         VARCHAR ( 1 );
   ...
   SET @do_task_load_criteria_into_@variables                   = 'N';
   SET @do_task_copy_inputable_@variables_into_@old_variables   = 'N';
   SET @do_task_load_factory_settings_into_inputable_@variables = 'N';
   SET @do_task_cancel_alerts_as_required                       = 'N';
   SET @do_task_replace_entire_target_row_with_@variables       = 'N';
   ...
   WHEN @submit_form = 'Show Criteria' THEN 
      SET @do_task_load_criteria_into_@variables                   = 'Y';
      SET @do_task_copy_inputable_@variables_into_@old_variables   = 'Y';

   WHEN @submit_form = 'Switch Display' THEN 
      SET @do_task_load_criteria_into_@variables                   = 'Y';
      SET @do_task_copy_inputable_@variables_into_@old_variables   = 'Y';

   WHEN @submit_form = 'Restore Factory Settings' THEN
      SET @do_task_load_criteria_into_@variables                   = 'Y';
      SET @do_task_copy_inputable_@variables_into_@old_variables   = 'Y';
      SET @do_task_load_factory_settings_into_inputable_@variables = 'Y';
      SET @do_task_cancel_alerts_as_required                       = 'Y';
      SET @do_task_replace_entire_target_row_with_@variables       = 'Y';
   ...
   
   IF @do_task_load_criteria_into_@variables = 'Y' THEN
      ...
   END IF;

   IF @do_task_copy_inputable_@variables_into_@old_variables = 'Y' THEN
      ...
   END IF;

   IF @do_task_load_factory_settings_into_inputable_@variables = 'Y' THEN
      ...
   END IF;

   IF @do_task_cancel_alerts_as_required = 'Y' THEN
      ...
   END IF;

   IF @do_task_replace_entire_target_row_with_@variables = 'Y' THEN
      ...
   END IF;
   ...

...and After

Here's what that excerpt might look like with the crude SET and IF statements changed to CALL and INTERNAL PROCEDURE statements:

CREATE PROCEDURE rroad_monitor_options (
   ...
BEGIN
   ...
   WHEN @submit_form = 'Show Criteria' THEN 
      CALL load_criteria_into_@variables();
      CALL copy_inputable_@variables_into_@old_variables();

   WHEN @submit_form = 'Switch Display' THEN 
      CALL load_criteria_into_@variables();
      CALL copy_inputable_@variables_into_@old_variables();

   WHEN @submit_form = 'Restore Factory Settings' THEN
      CALL load_criteria_into_@variables();
      CALL copy_inputable_@variables_into_@old_variables();
      CALL load_factory_settings_into_inputable_@variables();
      CALL cancel_alerts_as_required();
      CALL replace_entire_target_row_with_@variables();
   ...

   INTERNAL PROCEDURE load_criteria_into_@variables()
   BEGIN
      ...
   END;

   INTERNAL PROCEDURE copy_inputable_@variables_into_@old_variables()
   BEGIN
      ...
   END;

   INTERNAL PROCEDURE load_factory_settings_into_inputable_@variables()
   BEGIN
      ...
   END;

   INTERNAL PROCEDURE cancel_alerts_as_required()
   BEGIN
      ...
   END;

   INTERNAL PROCEDURE replace_entire_target_row_with_@variables()
   BEGIN
      ...
   END;
   ...

Since the main point behind an internal procedure is to take advantage of local variable scope rules (inheritance), I could live without parameter passing altogether... but here's what the full BNF might look like:

<internal_procedure>        ::= INTERNAL PROCEDURE <procedure_name>
                                   "(" [ <parameter_list> ] ")"
                                   [ RESULT "(" <result_set_column_list> ")" ]
                                   <begin_block>

<parameter_list>            ::= <parameter_specification> { "," <parameter_specification> }

<parameter_specification>   ::= [ <parameter_disposition> ]
                                  <parameter_name>
                                  <data_type>
                                [ <parameter_default> ]

<parameter_disposition>     ::= IN
                              | OUT
                              | INOUT  -- default

<parameter_default>         ::= DEFAULT <expression>

<result_set_column_list>    ::= <result_set_column> { "," <result_set_column> }

<result_set_column>         ::= <identifier> <data_type>