Stored Procedure

A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure (sometimes called a proc, sproc, StoPro, StoredProc, sp or SP) is actually stored in the database data dictionary.

Typical use for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures can consolidate and centralize logic that was originally implemented in applications. Extensive or complex processing that requires execution of several SQL statements is moved into stored procedures, and all applications call the procedures. One can use nested stored procedures by executing one stored procedure from within another.

Stored procedures are similar to user-defined functions (UDFs). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement.

CALL procedure(...)


EXECUTE procedure(...)

Stored procedures may return result sets, i.e. the results of a SELECT statement. Such result sets can be processed using cursors, by other stored procedures, by associating a result set locator, or by applications. Stored procedures may also contain declared variables for processing data and cursors that allow it to loop through multiple rows in a table. Stored procedure languages typically include IF, WHILE, LOOP, REPEAT, and CASE statements, and more. Stored procedures can receive variables, return results or modify variables and return them, depending on how and where the variable is declared.

Read more about Stored Procedure:  Implementation, Other Uses, Comparison With Dynamic SQL, Comparison With Functions, Comparison With Prepared Statements, Disadvantages

Famous quotes containing the word stored:

    The idea that information can be stored in a changing world without an overwhelming depreciation of its value is false. It is scarcely less false than the more plausible claim that after a war we may take our existing weapons, fill their barrels with cylinder oil, and coat their outsides with sprayed rubber film, and let them statically await the next emergency.
    Norbert Wiener (1894–1964)