You can create and call stored procedures in embedded SQL.
You can embed a CREATE PROCEDURE just like any other data definition statement, such as CREATE TABLE. You can also embed a CALL statement to execute a stored procedure. The following code fragment illustrates both creating and executing a stored procedure in embedded SQL:
EXEC SQL CREATE PROCEDURE pettycash( IN Amount DECIMAL(10,2) ) BEGIN UPDATE account SET balance = balance - Amount WHERE name = 'bank'; UPDATE account SET balance = balance + Amount WHERE name = 'pettycash expense'; END; EXEC SQL CALL pettycash( 10.72 ); |
If you want to pass host variable values to a stored procedure or to retrieve the output variables, you prepare and execute a CALL statement. The following code fragment illustrates the use of host variables. Both the USING and INTO clauses are used on the EXECUTE statement.
EXEC SQL BEGIN DECLARE SECTION; double hv_expense; double hv_balance; EXEC SQL END DECLARE SECTION; // Code here EXEC SQL CREATE PROCEDURE pettycash( IN expense DECIMAL(10,2), OUT endbalance DECIMAL(10,2) ) BEGIN UPDATE account SET balance = balance - expense WHERE name = 'bank'; UPDATE account SET balance = balance + expense WHERE name = 'pettycash expense'; SET endbalance = ( SELECT balance FROM account WHERE name = 'bank' ); END; EXEC SQL PREPARE S1 FROM 'CALL pettycash( ?, ? )'; EXEC SQL EXECUTE S1 USING :hv_expense INTO :hv_balance; |
For more information, see EXECUTE statement [ESQL], and PREPARE statement [ESQL].
Stored procedures with result sets
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |