Icon PREPARE

Prepares an SQL statement and binds it to a statement variable.

Syntax
PREPARE <StatementName> FROM <SQLStatement>

Usage
Use this statement to prepare a DDL, DML, or administrative SQL statement and bind it to a statement variable. The statement variable must have been previously declared in a DECLARE statement.

Using PREPARE pre-compiles the SQL statement so that it may be executed multiple times using the EXECUTE statement. This is especially useful with parameterized SQL statements.

For a SELECT statement, the PREPARE statement can be used to bind the statement to a cursor so that the cursor may then be opened using the OPEN statement.

Examples
-- This function looks up the sales tax
-- rate for a given state and county

CREATE FUNCTION LookupSalesTaxRate(IN State CHAR(2), IN County VARCHAR)
RETURNS DECIMAL(19,2)
BEGIN
   DECLARE TempCursor CURSOR FOR stmt;
   DECLARE Result DECIMAL(19,2) DEFAULT 0;

   PREPARE stmt FROM 'SELECT * FROM SalesTaxes WHERE State = ? AND County = ?';

   OPEN TempCursor USING State, County;

   IF (ROWCOUNT(TempCursor) > 0) THEN
      FETCH FIRST FROM TempCursor ('TaxRate') INTO Result;
   END IF;

   CLOSE TempCursor;

   RETURN Result;
END

SQL 2003 Standard Deviations
This statement deviates from the SQL 2003 standard in the following ways:

DeviationDetails
Dynamic SQLThe use of dynamic SQL for DDL, DML, and administrative statement execution instead of static SQL in procedures and functions is both an ElevateDB extension and a deviation from the standard.
Image