Advanced SQL (part 4): Stored Procedure

A stored procedure is a set of SQL statements with an assigned name, which are stored in a relational database management system (RDBMS) as a group, so it can be reused and shared by multiple programs. [1]

Stored procedures can access or modify data in a database, but it is not tied to a specific database or object, which offers a number of advantages:

• A stored procedure provides an important layer of security between the user interface and the database. It supports security through data access controls because end users may enter or change data, but do not write procedures. [1]
• A stored procedure preserves data integrity because information is entered in a consistent manner. [1]
• It improves productivity because statements in a stored procedure only must be written once. [1]
• Stored procedures offer advantages over embedding queries in a graphical user interface (GUI). Since stored procedures are modular, it is easier to troubleshoot when a problem arises in an application. Stored procedures are also tunable, which eliminates the need to modify the GUI source code to improve its performance. It’s easier to code stored procedures than to build a query through a GUI. [1]
• Use of stored procedures can reduce network traffic between clients and servers, because the commands are executed as a single batch of code. This means only the call to execute the procedure is sent over a network, instead of every single line of code…