Execute dynamic SQL

To execute an SQL statement, the database server must have the following information about the statement:
  • The type of statement, such as SELECT, DELETE, EXECUTE PROCEDURE, or GRANT
  • The names of any database objects, such as tables, columns, and indexes
  • Any WHERE-clause conditions, such as column names and matching criteria
  • Where to put any returned values, such as the column values from the select list of a SELECT statement
  • Values that need to be sent to the database server, such as the column values for a new row for an INSERT statement
If information in an SQL statement varies according to some conditions in the application, your program can use dynamic SQL to build the SQL statement at run time. The basic process to dynamically execute SQL statements consists of the following steps:
  1. Assemble the text of an SQL statement in a character-string variable.
  2. Use a PREPARE statement to have the database server examine the statement text and prepare it for execution.
  3. Execute the prepared statement with the EXECUTE or OPEN statement.
  4. Free dynamic resources that are used to execute the prepared statement.