Using Dynamic SQL with EXECUTE IMMEDIATE

The EXECUTE IMMEDIATE statement is the simplest way to use dynamic SQL. This method tells DB2 to perform the PREPARE and EXECUTE phase for the SQL statement in one step (see Figure 7.2).

Figure 7.2. The syntax diagram for EXECUTE IMMEDIATE.

 >>-EXECUTE--IMMEDIATE--sql-statement-------><

Figure 7.2 shows the syntax diagram for EXECUTE IMMEDIATE. The sql-statement parameter is required for the EXECUTE IMMEDIATE statement and is a VARCHAR parameter that contains the dynamic SQL statement you want to execute.

You would use EXECUTE IMMEDIATE if

You want to execute DML or DDL using dynamic SQL.
The SQL statement only needs to be executed once or infrequently.
The SQL statement is not a SELECT statement.

Consider Figure 7.3 that shows the updated employee table and where conditions of the update are generated at runtime.

Figure 7.3. An example of EXECUTE IMMEDIATE to execute dynamic SQL.

 CREATE PROCEDURE CHANGE_BONUS ( IN p_new_bonus DECIMAL                               , IN p_where_cond VARCHAR(1000)                               , OUT p_num_changes INT )     LANGUAGE SQL     SPECIFIC change_bonus1                        -- applies to LUW and iSeries  -- WLM ENVIRONMENT <env>                         -- applies to zSeries cb: BEGIN     DECLARE v_dynSQL VARCHAR(1000);                            -- (1)     SET v_dynSQL = 'UPDATE EMPLOYEE SET BONUS = ' ||           -- (2)                     CHAR(p_new_bonus) || ' WHERE ' ||                     p_where_cond;    EXECUTE IMMEDIATE v_dynSQL;                                 -- (3)    GET DIAGNOSTICS p_num_changes = row_count; END cb

In Figure 7.3, the SQL procedure CHANGE_BONUS takes two input parameters. The IN parameters, p_new_bonus and p_where_cond, specify the new bonus value and the WHERE condition for the change in bonus, respectively. The OUT parameter p_num_changes returns to the SQL procedure caller the number of records that were updated by the SQL procedure.

A variable called v_dynSQL used to store the dynamic SQL statement text is declared on Line (1). The variable is declared with a size that is sufficiently large enough to hold the entire statement. Otherwise, the SQL string is automatically truncated to fit. Next, the base UPDATE statement with two IN parameters is concatenated to produce the final SQL statement shown on Line (2). Finally, the UPDATE statement stored in the variable v_dynSQL on Line (3) is prepared and executed using the EXECUTE IMMEDIATE command.

Here is an example showing how to call the SQL procedure change_bonus. Establish a connection from the command line to the sample database. Then, execute the following CALL statement:

 CALL CHANGE_BONUS (1000, 'year(hiredate) < 1975', ?)

The procedure change_bonus is invoked to give every employee who was hired before 1975 a bonus of 1000. Notice that a question mark is used as a placeholder for the OUT parameter. Calling the SQL procedure from the command line will result in output that is similar to the following:

 Value of output parameters   --------------------------   Parameter Name  : P_NUM_CHANGES   Parameter Value : 20   Return Status = 0

From the output, 20 rows were updated by the SQL procedure.