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 the following figure, 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 1. Dynamic routine-name specification.

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 example, 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:

sales_person: An argument passed to the company_proc procedure.
current_month: The current month in the system date.
current_year: The current year in the system date.

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.