In its original form, SQL was not envisioned as a complete programming language. It was designed and implemented as a language for expressing database operations— creating database structures, entering data into the database, updating database data— and especially for expressing database queries and retrieving the answers. SQL could be used interactively by typing SQL statements at a keyboard, one by one. In this case, the sequence of database operations was determined by the human user. SQL could also be embedded within another programming language, such as COBOL or C. In this case, the sequence of database operations was determined by the flow of control within the COBOL or C program.
With stored procedures, several capabilities normally associated with programming languages are grafted onto the SQL language. Sequences of extended SQL statements are grouped together to form SQL programs or procedures. The specifics vary from one implementation to another, but generally, these capabilities are provided:
- Conditional execution. An IF…THEN…ELSE structure allows a SQL procedure to test a condition and carry out different operations depending on the result.
- Looping. A WHILE or FOR loop or similar structure allows a sequence of SQL operations to be performed repeatedly, until some terminating condition is met. Some implementations provide a special cursor-based looping structure to process each row of query results.
- Block structure. A sequence of SQL statements can be grouped into a single block and used in other flow-of-control constructs as if the statement block were a single statement.
- Named variables. A SQL procedure may store a value that it has calculated, retrieved from the database, or derived in some other way into a program variable, and later retrieve the stored value for use in subsequent calculations.
- Named procedures. A sequence of SQL statements may be grouped together, given a name, and assigned formal input and output parameters, like a subroutine or function in a conventional programming language. Once defined in this way, the procedure may be called by name, passing it appropriate values for its input parameters. If the procedure is a function returning a value, it may be used in SQL value expressions.
Collectively, the structures that implement these capabilities form a stored procedure language (SPL).
Stored procedures were first introduced by Sybase in the original Sybase SQL Server product. Much of the original enthusiasm for stored procedures was because of their performance impact in a client/server database architecture. Without stored procedures, every SQL operation requested by an application program (running on the client computer system) would be sent across the network to the database server, and would wait for a reply message to be returned across the network. If a logical transaction required six SQL operations, six network round trips were required. With stored procedures, the sequence of six SQL operations could be programmed into a procedure and stored in the database. The application program would simply request the execution of the stored procedure and await the results. In this way, six network round trips could be cut to one round trip—the request and reply for executing the stored procedure.
Stored procedures proved to be a natural fit for the client/server model, and Sybase used them to establish an early lead with this architecture. A competitive response quickly followed from many of the other DBMS vendors. Today, most enterprise DBMS products provide a stored procedure capability, and the benefits of stored procedures in corporate databases has expanded considerably beyond the early focus on network performance. Stored procedures are less relevant for other types of specialized DBMS systems, such as data warehousing systems or in-memory databases. Some DBMS products have modeled their SPL structures on C or Pascal language constructs. Others have tried to match the style of the SQL Data Manipulation Language (DML) and Data Definition Language (DDL) statements. As a result, while stored procedure concepts are very similar from one SQL dialect to another, the specific syntax varies considerably.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.