Stored Procedure

A Stored Procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a sproc or SP) are actually stored in the database.

A Stored Procedure is a group of SQL statements that form a logical unit and perform a particular task, and they are used to encapsulate a set of operations or queries to execute on a database server.

Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedures reduce network traffic and improve performance. Additionally, stored procedures can be used to help ensure the integrity of the database.
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(…)

Stored procedures can 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. The standard Structured Query Language provides 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.

How to test Stored Procedure?