Create and Execute Stored Procedures

You can create a procedure in the current database.

In versions of SAP ASE earlier than 15.5, all referenced objects where required to exist when a procedure was created. The deferred name resolution feature allows objects, except for user-created datatype objects, to be resolved when the stored procedure is initially executed.

Deferred name resolution uses the deferred name resolution configuration parameter, which operates at the server level, or set deferred_name_resolution parameter, which operates at the connection level.

The default behavior is to resolve the objects before execution. You must explicitly indicate deferred name resolution using the configuration option deferred name resolution, or the set parameter.

See the System Administration Guide: Volume 1, and the Reference Manual: Commands.

Permission to issue create procedure defaults to the database owner, who can transfer it to other users.

• Deferred Name Resolution Usage
  When deferred name resolution is active objects inside procedures are resolved at execution time, instead of at creation time.
• Parameters
  A parameter is an argument to a stored procedure. You can optionally declare one or more parameters in a create procedure statement. The value of each parameter named in a create procedure statement must be supplied by the user when the procedure is executed.
• Default Parameters
  You can assign a default value for a parameter in the create procedure statement. This value, which can be any constant, is used as the argument to the procedure if the user does not supply one.
• Using Multiple Parameters
  Examples are provided for using multiple parameters in a procedure.
• LOB Datatypes in Stored Procedures
  You can declare a large object (LOB) text, image, or unitext datatype for a local variable, and pass that variable as an input parameter to a stored procedure.
• Procedure Groups
  You can optionally use a semicolon and integer number after the name of a procedure in the create procedure, and execute statements, to group procedures of the same name so that they can be dropped together with a single drop procedure statement.
• with recompile in create procedure
  In the create procedure statement, the optional clause with recompile comes immediately before the SQL statements. It instructs SAP ASE not to save a plan for this procedure. A new plan is created each time the procedure is executed.
• with recompile in execute
  In the execute statement, the optional clause with recompile comes after any parameters. It instructs SAP ASE to compile a new plan, which is used for subsequent executions.
• Nesting Procedures
  Nesting occurs when one stored procedure or trigger calls another. The nesting level is incremented when the called procedure or trigger begins execution and is decremented when the called procedure or trigger completes execution.
• Temporary Tables in Stored Procedures
  You can create and use temporary tables in a stored procedure, but they exists only for the duration of the stored procedure that creates it. When the procedure completes, SAP ASE automatically drops temporary tables.
• Set Options in Stored Procedures
  You can use almost all of the set command options inside stored procedures. The set option remains in effect during the execution of the procedure, and most options revert to the former setting at the close of the procedure.
• Execution of Stored Procedures
  You can execute stored procedures after a time delay, or remotely.