Using dynamic SQL in stored procedures
Dynamic SQL is used when the SQL statement is not known at compile time. In such cases, we use PREPARE and EXECUTE statements to execute a dynamic SQL, where we get the complete SQL statement at runtime. The prepared statement is based on either a host variable or a string expression. At runtime, we make this value available for DB2 to process it. We can implement dynamic SQL in stored procedures in the same manner as in other applications. In this recipe, we will focus on using dynamic SQL in stored procedures.
How to do it...
Executing an SQL statement dynamically: To execute an SQL statement dynamically, we need to first prepare it and then execute it. The statement can be prepared from a declared variable or directly from an SQL statement.
The following example prepares and executes an UPDATE statement on the EMPLOYEE table, based on input parameter value:
1. Define the input and output parameters of the procedure along with any optional clauses...
