Runs a system procedure or a user-defined stored procedure.
exec sql [at connection_name] exec [:status_var = status_value] procedure_name [([[@parameter_name =]param_value [out[put]]],...)] [into :hostvar_1 [:indicator_1] [, hostvar_n [indicator_n,…]]] [with recompile] end-exec
Do not confuse the exec statement
with the Embedded SQL execute statement;
they are not related. The Embedded SQL exec statement is, however,
the equivalent of the Transact-SQL execute statement.
A host variable to receive the return status of the stored procedure.
The value of the stored procedure return status variable status_var.
The name of the stored procedure to be executed.
The name(s) of the stored procedure’s parameter(s).
A host variable or literal value.
Indicates that the stored procedure returns a parameter value. The matching parameter in the stored procedure must also have been created using the output keyword.
Causes row data returned from the stored procedure to be stored in the specified host variables (hostvar_1 through hostvar_n). Each host variable can have an indicator variable.
Causes Adaptive Server Enterprise to create a new query plan for this stored procedure each time the procedure executes.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 TITLE-ID PIC X(6).
01 TOTAL-DISC PIC S9(9).
01 RET-STATUS PIC S9(9).
EXEC SQL END DECLARE SECTION END-EXEC.
...
EXEC SQL CREATE PROC get_sum_discounts(@title_id tid,
@discount int output) as
begin
select @discount = sum (qty*discount)
from salesdetail
where title_id = @title_id
end
END-EXEC.
EXEC SQL SET CHAINED ON END-EXEC.
DISPLAY "TITLE ID ? ".
ACCEPT TITLE-ID.
EXEC SQL EXEC :RET-STATUS = get_sum_discounts
:TITLE-ID, :TOTAL-DISC OUT END-EXEC.
DISPLAY "TOTAL DISCOUNTS FOR TITLE ID ", TITLE-ID," = ",TOTAL-DISC.
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 PUB-ID PIC X(4).
01 NAME PIC X(25).
01 CITY PIC X(25).
01 STATE PIC X(2).
01 RET-STATUS PIC S9(9).
EXEC SQL END DECLARE SECTION END-EXEC.
...
EXEC SQL CREATE PROC get_publishers(@pubid char(4))
as
select pub_name, city, state from
publishers where pub_id = @pubid
END-EXEC.
DISPLAY " DETAIL RECORD FOR PUBLISHER ? ".
ACCEPT PUB-ID.
EXEC SQL EXEC :RET-STATUS = get_publishers :PUB-ID
INTO :NAME, :CITY, :STATE END-EXEC.
IF RET-STATUS = 0
DISPLAY " PUBLISHER NAME : ", NAME
DISPLAY " CITY : ", CITY
DISPLAY " STATE : ", STATE
Only one select statement can return rows to the client application.
If the stored procedure contains select statements that can return row data, you must use one of two methods to store the data. You can either use the into clause of the exec statement or declare a cursor for the procedure. If you use the into clause, the stored procedure must not return more than one row of data, unless the host variables that you specify are arrays.
The value param_value can be a host variable or literal value. If you use the output keyword, param_value must be a host variable.
You can specify the output keyword for parameter_name only if that keyword was also used for the corresponding parameter of the create procedure statement that created procedure_name.
The Embedded SQL exec statement works much like the Transact-SQL execute statement.
declare cursor (stored procedure), select
