SQL Statements - Preparing and Executing

Ways to Use the SQL Language

Dynamic SQL Statements in Stored Procedures

There are situations when a SQL statement cannot be used with other statements in a stored procedure, as follows:

• When a table or column name that the statement works with is only know after the result of a previous statement.
• When the statement points to an object that was created by the previous statement.

It is required in the above cases to postpone statement analysis and optimization before statement execution. The SQL_execute function provides this ability. This function accepts a SQL statement or a group of SQL statements as a parameter, analyzes and optimizes these statements, then executes the statements.

SQL statement execution with the SQL_execute statement is less effective than inserting the statement directly into the stored procedure (analysis and optimization is done before each call), but it is more flexible (the statement may be dynamically created before execution).

When calling a SQL statement using the SQL_execute function you can use variables that exist in the SQL procedure that called SQL_execute. In the following example the variable 'J' is passed as a parameter value, the variable 'I' is declared globally inside the procedure, and the variable 'K' is local inside the statement. Each of these variables may be used in the SQL_execute.

PROCEDURE MY_PROC(IN j INT);
BEGIN
  DECLARE i INT DEFAULT 1;
  BEGIN
    DECLARE k INT DEFAULT 3;
    DECLARE stmt CHAR(80);
    SET stmt = 'INSERT INTO ' + 'My_Tab' + '(number1,nunmber2,number3) VALUES (i,j,k);';
    CALL SQL_execute(stmt);
  END;
END;

If an error occurs when compiling a SQL statement, the SQL_execute function returns a positive error number and the statement is not executed.

Errors can be processed by handlers. If the error is not processed by a CONTINUE handler, consecutive SQL statements are not executed.

SQL Statements - Preparing and Executing

Ways to Use the SQL Language