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.

Stored procedures can:

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.

Related concepts
Extended Stored Procedures Usage