Deallocates a dynamic SQL statement that was prepared in a prepare statement.
exec sql [at connection_name] deallocate prepare statement_name end-exec
The identifier assigned to the dynamic SQL statement when the statement was prepared.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 CMDBUF PIC X(120).
01 STATE PIC X(3).
EXEC SQL END DECLARE SECTION END-EXEC.
...
* The 'select into table' statement returns no results
* to the program, so it does not need a cursor.
MOVE "select * into tmp from authors where state = ?"
TO CMDBUF.
DISPLAY "STATE ? ".
ACCEPT STATE.
EXEC SQL PREPARE dynstmt FROM :CMDBUF END-EXEC.
EXEC SQL EXECUTE dynstmt USING :STATE END-EXEC.
EXEC SQL DEALLOCATE PREPARE dynstmt END-EXEC.
EXEC SQL COMMIT WORK END-EXEC.
A statement must be prepared before it is deallocated. Attempting to deallocate a statement that has not been prepared results in an error.
statement_name must uniquely identify a statement buffer and must conform to the SQL identifier rules for naming variables. statement_name can be either a literal or a character array host variable.
The deallocate prepare statement closes and deallocates any dynamic cursors declared for statement_name.
WARNING! If you are using persistent binds in your Embedded SQL program, use the deallocate prepare statement carefully. Needlessly deallocating prepared statements can negate the advantage of persistent binds.
declare cursor (dynamic), execute, execute immediate, prepare