Dynamic Routine-Name Specification

Dynamic routine-name specification allows you to execute an SPL routine from another SPL routine, by building the name of the called routine within the calling routine. Dynamic routine-name specification simplifies how you can write an SPL routine that calls another SPL routine whose name is not known until runtime. The database server lets you specify an SPL variable instead of the explicit name of an SPL routine in the EXECUTE PROCEDURE or EXECUTE FUNCTION statement.

In Figure 463, the SPL procedure company_proc updates a large company sales table and then assigns an SPL variable named salesperson_proc to hold the dynamically created name of an SPL procedure that updates another, smaller table that contains the monthly sales of an individual salesperson.

Figure 463.

CREATE PROCEDURE company_proc ( no_of_items INT,
   itm_quantity SMALLINT, sale_amount MONEY, 
   customer VARCHAR(50), sales_person VARCHAR(30) )

DEFINE salesperson_proc VARCHAR(60);

-- Update the company table
INSERT INTO company_tbl VALUES (no_of_items, itm_quantity,
   sale_amount, customer, sales_person); 

-- Generate the procedure name for the variable salesperson_proc
LET salesperson_proc = sales_person || "." || "tbl" ||
   current_month || "_" || current_year || "_proc" ;

-- Execute the SPL procedure that the salesperson_proc 
-- variable specifies
EXECUTE PROCEDURE salesperson_proc (no_of_items,
   itm_quantity, sale_amount, customer)
END PROCEDURE;

In Figure 463, the procedure company _proc accepts five arguments and inserts them into company_tbl. Then the LET statement uses various values and the concatenation operator || to generate the name of another SPL procedure to execute. In the LET statement:

Therefore, if a salesperson named Bill makes a sale in July 1998, company_proc inserts a record in company_tbl and executes the SPL procedure bill.tbl07_1998_proc, which updates a smaller table that contains the monthly sales of an individual salesperson.

Rules for Dynamic Routine-Name Specification

You must define the SPL variable that holds the name of the dynamically executed SPL routine as CHAR, VARCHAR, NCHAR, or NVARCHAR type. You must also give the SPL variable a valid and non-NULL name.

The SPL routine that the dynamic routine-name specification identifies must exist before it can be executed. If you assign the SPL variable the name of a valid SPL routine, the EXECUTE PROCEDURE or EXECUTE FUNCTION statement executes the routine whose name is contained in the variable, even if a built-in function of the same name exists.

In an EXECUTE PROCEDURE or EXECUTE FUNCTION statement, you cannot use two SPL variables to create a variable name in the form owner.routine_name. However, you can use an SPL variable that contains a fully qualified routine name, for example, bill.proc1. Figure 464 shows both cases.

Figure 464.

EXECUTE PROCEDURE owner_variable.proc_variable
   -- this is not allowed ;

LET proc1 = bill.proc1;
EXECUTE PROCEDURE proc1 -- this is allowed ;