Stored procedures with output parameters

SAP® ASE stored procedures must be called with the input and output parameters specification in the USING clause of the EXECUTE, OPEN or FOREACH instruction. As in normal dynamic SQL, parameters must correspond by position and the IN/OUT/INOUT options must match the parameter definition of the stored procedure.

To execute the stored procedure, you must use a specific syntax to have the database driver identify the statement as an RPC call. The syntax of an RPC call must be:

!rpc procedure-name ( [ @param-name [,...] ] )

The parameter names must be specified, with the same names as the arguments of the stored procedure, because the ODI driver must bind stored procedure parameters by name.

Example:

PREPARE stmt FROM "!rpc update_account ( @custid, @old, @new )"

Here is a complete example creating and calling a stored procedure with output parameters:

MAIN
   DEFINE n INTEGER
   DEFINE d DECIMAL(6,2)
   DEFINE c VARCHAR(200)
   DATABASE test1
   EXECUTE IMMEDIATE
                   "create procedure proc1"
                || "   @v1 integer,"
                || "   @v2 decimal(6,2) output,"
                || "   @v3 varchar(20) output"
                || " as begin"
                || "  set @v2 = @v1 + 0.23"
                || "  set @v3 = 'Value = ' || cast(@v1 as varchar)"
                || "end"
   PREPARE stmt FROM "!rpc proc1( @v1, @v2, @v3 )"
   LET n = 111
   EXECUTE stmt USING n IN, d OUT, c OUT
   DISPLAY d
   DISPLAY c
END MAIN