When you run a stored procedure, Adaptive Server prepares an execution plan so that the procedure’s execution is very fast. Stored procedures can:
Take parameters
Call other procedures
Return a status value to a calling procedure or batch to indicate success or failure and the reason for failure
Return values of parameters to a calling procedure or batch
Be executed on remote Adaptive Servers
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 Adaptive 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 Adaptive 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, Adaptive Server’s query processor analyzes it and prepares an execution plan that is ultimately 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.
Adaptive Server 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.
