Intelligent Enterprises of the 21st Century

Dynamic CALL Statements

In LUW and iSeries the CALL statement can be dynamically prepared. You can specify parameter markers through the use of host variables. However, the syntax, for LUW and iSeries differ slightly:

• In LUW, the USING clause of the EXECUTE statement is used to provide values for the IN and INOUT parameter. The INTO clause can be used for receiving values from INOUT and OUT parameters of dynamically called procedures. Refer to Figure 7.5 for the complete syntax diagram of the EXECUTE statement.

• In iSeries, the USING clause of the EXECUTE statement is used for all parameter modes: IN, OUT, INOUT.

Figure 7.8 shows an example of how to use the INTO clause.

Figure 7.8. An example of using EXECUTE ... USING... INTO... for dynamically prepared CALL statements.

CREATE PROCEDURE dynamicCall( IN procschema VARCHAR(50) , IN procname VARCHAR(50) , OUT p_total INTEGER ) LANGUAGE SQL SPECIFIC dynamiccall -- applies to LUW and iSeries dc2: BEGIN DECLARE v_dynSQL VARCHAR(200); DECLARE v_p1 INT; DECLARE v_p2 INT; DECLARE v_p3 INT; SET v_p1=100; SET v_p2=200; -- default to user schema if not supplied SET procschema = COALESCE (procschema, USER); -- (1) SET v_dynSQL = 'CALL ' || rtrim(procschema) || '.' || ltrim(procname) || '(?,?,?)'; -- (2) PREPARE v_stmt FROM v_dynSQL; /* Assumption - SQL procedure being called * has 3 parameters : * v_p1 - IN * v_p2 INOUT * v_p3 OUT */ EXECUTE v_stmt INTO v_p2, v_p3 USING v_p1, v_p2; -- applies to LUW -- (3) --EXECUTE v_stmt USING v_p1, v_p2, v_p3; -- applies to iSeries SET p_total = v_p2 + v_p3; END dc2

Note

In zSeries, support for dynamic CALL statements in SQL procedures is only available from some ODBC or CLI drivers such as the ones provided by IBM. Thus, there is no workaround to the sample SQL procedure shown in Figure 7.8.

In Figure 7.8, the SQL procedure dynamicCall takes two parameters: a schema and procedure name. The procschema parameter is checked whether it has a NULL value. If NULL is supplied, the current connected user ID will be used as the default schema, shown on Line (1). A CALL statement is then formed by concatenating the SQL procedure schema and name, using the LTRIM and RTRIM functions to strip off blank spaces that may cause problems, as shown on Line (2).

Tip

For best performance, it is always better to use CALL with fully qualified SQL procedure names.

For the purposes of this example, we can assume that whatever SQL procedure is called, the SQL procedure will have exactly three parameters; those parameters are IN, INOUT, and OUT. If an SQL procedure is called that differs in its parameter requirements, the CALL will fail.

After the CALL statement has been prepared, it is executed USING the variables v_p1 and v_p2 as the input values to the IN and INOUT parameters, respectively. The INTO clause is supported by v_p2 and v_p3 to receive values returned from the called SQL procedure.

The dynamically prepared statement was a CALL statement that had v_p2 defined as an INOUT parameter. The statement will run as expected in the rewritten form.