stored procedure


stored procedure

(database)A subroutine stored in a database and executedby the database management system. The subroutine may bewritten in the same language in which the database is queriedand may be precompiled to improve performance.

Typically a stored procedure encapsulates some businessprocess. Performing this on the database server avoids thenetwork overhead of transferring input data to the client forprocessing. This would be particularly significant ifprocessing lots of data and returning a small result set likea total or maximum. Stored procedures also provide consistentimplementation of the business logic to clients written indifferent languages and running in different environments.

Some financial systems allow databases access through storedprocedures alone, this restricts actions on the data to asmall number of auditable queries.

Sybase SQL Server (Adaptive Server Enterprise) was the firstcommercially successful RDBMS to support stored procedures.

stored procedure

In a database management system (DBMS), it is an SQL program that is stored in the database which is executed by calling it directly from the client or from a database trigger. When the SQL procedure is stored in the database, it does not have to be replicated in each client. This saves programming effort especially when different client user interfaces and development systems are used. Triggers and stored procedures are built into DBMSs used in client/server environments.