SQL procedures, like external stored procedures, can issue dynamic SQL statements. If your dynamic SQL statement does not include parameter markers and you plan to execute it only once, use the EXECUTE IMMEDIATE statement.
If your dynamic SQL statement contains parameter markers, you must use the PREPARE and EXECUTE statements. If you you plan to execute a dynamic SQL statement multiple times, it might be more efficient to issue a single PREPARE statement and to issue the EXECUTE statement multiple times rather than issuing the EXECUTE IMMEDIATE statement each time. To use the PREPARE and EXECUTE statements to issue dynamic SQL in your SQL procedure, you must include the following statements in the SQL procedure body:
Step 1. | Declare a variable of type VARCHAR that is large enough to hold your dynamic SQL statement using a DECLARE statement. |
Step 2. | Assign a statement string to the variable using a SET statement. You cannot include variables directly in the statement string. Instead, you must use the question mark ('?') symbol as a parameter marker for any variables used in the statement. |
Step 3. | Create a prepared statement from the statement string using a PREPARE statement. |
Step 4. | Execute the prepared statement using an EXECUTE statement. If the statement string includes a parameter marker, use a USING clause to replace it with the value of a variable.
|
| Note: | Statement names defined in PREPARE statements for SQL procedures are treated as scoped variables. Once the SQL procedure exits the scope in which you define the statement name, DB2 can no longer access the statement name. Inside any compound statement, you cannot issue two PREPARE statements that use the same statement name. |
Example: Dynamic SQL statements: The following example shows an SQL procedure that includes dynamic SQL statements.
The procedure receives a department number (deptNumber) as an input parameter. In the procedure, three statement strings are built, prepared, and executed. The first statement string executes a DROP statement to ensure that the table to be created does not already exist. This table is named DEPT_deptno_T, where deptno is the value of input parameter deptNumber. A CONTINUE HANDLER ensures that the SQL procedure will continue if it detects SQLSTATE 42704 ("undefined object name"), which DB2 returns from the DROP statement if the table does not exist. The second statement string issuees a CREATE statement to create DEPT_deptno_T. The third statement string inserts rows for employees in department deptno into DEPT_deptno_T. The third statement string contains a parameter marker that represents deptNumber. When the prepared statement is executed, parameter deptNumber is substituted for the parameter marker.
CREATE PROCEDURE create_dept_table
(IN deptNumber VARCHAR(3), OUT table_name VARCHAR(30))
LANGUAGE SQL
BEGIN
DECLARE stmt VARCHAR(1000);
-- continue if sqlstate 42704 ('undefined object name')
DECLARE CONTINUE HANDLER FOR SQLSTATE '42704'
SET stmt = '';
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET table_name = 'PROCEDURE_FAILED';
SET table_name = 'DEPT_'||deptNumber||'_T';
SET stmt = 'DROP TABLE '||table_name;
PREPARE s1 FROM stmt;
EXECUTE s1;
SET stmt = 'CREATE TABLE '||table_name||
'( empno CHAR(6) NOT NULL, '||
'firstnme VARCHAR(12) NOT NULL, '||
'midinit CHAR(1) NOT NULL, '||
'lastname VARCHAR(15) NOT NULL, '||
'salary DECIMAL(9,2))';
PREPARE s2 FROM STMT;
EXECUTE s2;
SET stmt = 'INSERT INTO '||table_name || ' ' ||
'SELECT empno, firstnme, midinit, lastname, salary '||
'FROM employee '||
'WHERE workdept = ?';
PREPARE s3 FROM stmt;
EXECUTE s3 USING deptNumber;
END