Executes a prepared statement in a stored procedure.
ANSI Compliance
ANSI/ISO SQL:2011-compliant.
Required Privileges
None.
Invocation
Executable.
Stored procedures only.
Syntax
EXECUTE statement_name [ USING { SQL_identifier | SQL_parameter } [,...] ]
Syntax Elements
- statement_namee
- The name associated with the previously prepared statement.
- SQL_identifier
- A valid SQL identifier.
- SQL_parameter
- An SQL parameter.
Usage Notes
- EXECUTE cannot be used with any of the following: a dynamic data returning statement or a dynamic multistatement request
- EXECUTE itself cannot be performed as a dynamic SQL statement.
The following rules apply to the USING clause:
- The USING clause identifies variables used as input to the SQL statement specified by statement_name.
- The specified statement-name must be a valid and declared prior to the EXECUTE statement.
- The number of variables specified must be the same as the number of parameter markers (the questions mark character) in the identified statement. The nth variable must correspond to the nth parameter marker.
- The arguments must be compatible with the target. Necessary compatible conversion will be performed.
Example: Executing a Prepared Statement in a Stored Procedure
CREATE PROCEDURE sales_update(store_table VARCHAR(10),
item INTEGER,
price DECIMAL(8,2) )
BEGIN
DECLARE sql_stmt VARCHAR(100);
SET sql_stmt = 'UPDATE ' || store_table || ' SET store_price=' || price || '
WHERE store_item =' || item;
PREPARE stmt1 FROM SQL_stmt;
EXECUTE stmt1;
END;
Example: Executing a Prepared Statement in a Stored Procedure to Update the Price of an Item
CREATE PROCEDURE sales_update(store_table VARCHAR(10), item INTEGER, price DECIMAL(8,2) ) BEGIN DECLARE price_read DECIMAL(8,2); DECLARE sql_stmt VARCHAR(100); SET sql_stmt = 'UPDATE ' || store_table || ' SET store_price=? WHERE store_item = ?;'; PREPARE stmt1 FROM sql_stmt; EXECUTE stmt1 USING price, item; END;