Modern database servers provide support for stored procedures. A stored procedure is a script written in a server-specific dialect of SQL. Such scripts are stored in the database and executed by a database server process upon request.
The main benefits are the close proximity to the data and reusability of the procedure. Applications invoke stored procedures by name with parameters that affect the execution result.
A popular reason to implement a stored procedure is the need to produce a custom result set that cannot be achieved with a SELECT statement. Sometimes the database table data needs to be split into multiple streams, merged, pivoted, and conditionally processed over multiple iterations. A custom result set is returned to the client application in the stored procedure output.
Frequently, it takes a longer time to produce a custom result set with a stored procedure when compared to a straightforward SELECT statement. This creates a unique challenge in presenting the output of stored procedures in the user interface of applications. End users may be willing to wait for initial results, but will quickly grow irritated if every single interaction with the application takes a long time to complete.
Output caching must be employed when working with the stored procedures designed to produce custom result sets.