User-defined stored procedures

With Embedded SQL version 11.1 and later, you can execute stored procedures with select statements that return data rows. Stored procedures can return results to your program through output parameters and through a return status variable.

Stored procedure parameters can be either input or both input and output. For details on stored procedures, see the Transact-SQL User’s Guide.

Valid stored procedure names consist of uppercase and lowercase letters and the characters $, _, and #.

Do not include the use statement in a stored procedure.

To execute a stored procedure, use the following syntax:

exec sql [at connection_name] 

  exec [:status_variable = status_value] procedure_name
 [([[@parameter_name =]parameter_value [out[put]]],...)]

  [into :hostvar_1 [:indicator_1]

  [, hostvar_n [indicator_n, ...]]]

  [with recompile] 

 end-exec

where:

• status_variable can return either an Adaptive Server return status value or a return code, which either indicates that the stored procedure completed successfully or gives the reasons for the failure. Negative status values are reserved for Adaptive Server use. See the Transact-SQL User’s Guide for a list of return status values for stored procedures.

• status_value is the value of the stored procedure return status variable status_variable.

• procedure_name is the name of the stored procedure to execute.

• parameter_name is the name of a variable in the stored procedure. You can pass parameters either by position or by name, using the @parameter_name format. If one parameter is named, all of them must be named. For more information on stored procedures, see the Transact SQL User’s Guide.

• parameter_value is a literal constant or host variable whose value is passed to the stored procedure. If it is a host variable, you can associate an indicator with it. Note that this variable has no keyword associated with it.

• output 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.

• into:hostvar_1 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.

• indicator_n is a two-byte host variable declared in a previous declare section. If the value for the associated hostvar_n is null, the indicator variable is set to -1 when the row data is retrieved. If truncation occurs, the indicator variable is set to the actual length of the result column. Otherwise, the indicator variable is 0.

• with recompile causes Adaptive Server to create a new query plan for this stored procedure each time the procedure executes.

The following example shows a call to a stored procedure where RET-CODE is a status variable, a_proc is the stored procedure, PAR–1 is an input parameter, and PAR–2 is an output parameter:

exec sql begin declare section end-exec 

  01  PAR-1              PIC S9(9) COMP. 

  01  PAR-2              PIC S9(9) COMP. 

  01  RET-CODE           PIC S9(4) COMP. 

 exec sql end declare section end-exec 

     . . . 

     exec sql exec :RET-CODE=a_proc :PAR-1, 

                   :PAR-2 out end-exec. 

The next example demonstrates the use of a stored procedure that retrieves data rows. The name of the stored procedure is “get_publishers”:

exec sql begin declare section end-exec.

  01  PUB-ID            PIC X(4).

  01  NAME              PIC X(45).

  01  CITY              PIC X(25).

  01  STATE             PIC X(2).

  01  RET-CODE          PIC S9(9).

 exec sql end declare section end-exec.

    . . .

 exec sql exec :RET-CODE = get_publishers :PUB-ID

                   into :NAME :CITY :STATE END-EXEC.