Stored Procedures

A stored procedure is a named collection of SQL statements or control-of-flow language. You can create stored procedures for commonly used functions, and to improve performance. SAP ASE also provides system procedures for performing administrative tasks that update the system tables.

The ability to write stored procedures greatly enhances the power, efficiency, and flexibility of SQL. Compiled procedures dramatically improve the performance of SQL statements and batches. In addition, stored procedures on other servers can be executed if both your server and the remote server are set up to allow remote logins. You can write triggers on your local server that execute procedures on a remote server whenever certain events, such as deletions, updates, or inserts, take place locally.

Stored procedures differ from ordinary SQL statements and from batches of SQL statements in that they are precompiled. The first time you run a procedure, the SAP ASE query processor analyzes it and prepares an execution plan that is, after successful execution, stored in a system table. Subsequently, the procedure is executed according to the stored plan. Since most of the query processing work has already been performed, stored procedures execute almost instantly.

SAP ASE supplies a variety of stored procedures as convenient tools for the user. The procedures stored in the sybsystemprocs database whose names begin with “sp_” are known as system procedures, because they insert, update, delete, and report on data in the system tables.

The Reference Manual: Procedures includes a complete list of all SAP-provided system procedures.

You can also create and use extended stored procedures to call procedural language functions from SAP ASE.

• Examples
  Examples are provided to illustrate how to create and execute a stored procedure.
• Permissions
  Stored procedures can serve as security mechanisms, since a user can be granted permission to execute a stored procedure, even if she or he does not have permissions on the tables or views referenced in it, or permission to execute specific commands.
• Performance
  As a database changes, you can optimize the original query plans used to access its tables by recompiling them. This saves you from having to find, drop, and re-create every stored procedure and trigger.
• Create and Execute Stored Procedures
  You can create a procedure in the current database.
• Deferred Compilation in Stored Procedures
  SAP ASE optimizes stored procedures when they are first executed, as long as the values that are passed for variables are available.
• Information Returned From Stored Procedures
  Stored procedures return the certain types of information, including return status and parameters, and the privileges that are assigned to the user who executed each procedure.
• Restrictions Associated with Stored Procedures
  Several restrictions apply when creating stored procedures.
• Rename Stored Procedures
  Use sp_rename to rename stored procedures.
• Stored Procedures as Security Mechanisms
  You can use stored procedures as security mechanisms to control access to information in tables, and to control the ability to perform data modification.
• Dropping Stored Procedures
  Use drop procedure to remove stored procedures.
• System Procedures
  System procedures are shortcuts for retrieving information from the system tables, and mechanisms for performing database administration and other tasks that involve updating system tables.
• Get Information About Stored Procedures
  Several system procedures provide information from the system tables about stored procedures.