User-defined stored procedures

With Embedded SQL, 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, output, or both input and output. For details on stored procedures, see the Adaptive Server Enterprise Transact-SQL Users Guide.

Valid stored procedure names consist of upper- and lowercase letters of the alphabet, “$”, “_”, and “#”.

Do not include the use statement in a stored procedure.

To execute a stored procedure, use the following syntax:

exec [[:status_variable =]status_value] procedure_name

[([[@parameter_name=]parameter_value [out[put]]],...)]

 [into :hostvar_1 [:indicator_1]

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

 [with recompile];

where:

• status_variable can return either an Adaptive Server Enterprise 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 Enterprise use. See the Adaptive Server Enterprise Transact-SQL Users 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. If one parameter is named, all of them must be named. See the Adaptive Server Enterprise Transact-SQL Users Guide.

• parameter _value is a literal constant whose value is passed to the stored procedure.

• 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 2-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 Enterprise 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 retcode is a status variable, a_proc is the stored procedure, par1 is an input parameter, and par2 is an output parameter:

exec sql begin declare section; 

 CS_INT  par1; 

 CS_INT  par2; 

 CS_SMALLINT  retcode; 

 exec sql end declare section; 

     ... 

 exec sql exec :retcode = a_proc :par1, :par2 out;

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;

 CS_CHAR  pub_id(4);

 CS_CHAR  name(45);

 CS_CHAR  city(25);

 CS_CHAR  state(2);

 CS_SMALLINT  retcode;

 exec sql end declare section;

    . . .

 exec sql exec :retcode = get_publishers :pub_id

                   into :name :city :state;