Stored Procedure

A stored procedure is a subroutine available to connected relational database system applications. A stored procedure is a set of Structured Query Language (SQL) statements with an assigned name, which are stored in a relational database management system as a group, so it can be reused and shared by multiple programs. A stored procedure can be invoked by triggers, other stored procedures, and applications such as Java, Python, PHP. Stored procedures must be called or invoked, as they are sets of SQL and programming commands that perform very specific functions. Most major relational database systems (e.g., SQL Server, Oracle, MySQL, Postgres and others) provide support for stored procedures.

Create Stored Procedure

DELIMITER //
 
CREATE PROCEDURE `procedure-name` ()
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'comment text'
BEGIN
    sql statements goes here
END//
DELIMITER

The first part of the statement creates the procedure. The next clauses defines the optional characteristics of the procedure. Then you have the name and finally the body or routine code.

Stored procedure names are case insensitive, and you cannot create procedures with the same name. Inside a procedure body, you can’t put database-manipulation statements.

The four characteristics of a procedure are:

• Language : For portability purposes; the default value is SQL.
• Deterministic : If the procedure always returns the same results, given the same input. This is for replication and logging purposes. The default value is NOT DETERMINISTIC.
• SQL Security : At call time, check privileges of the user. INVOKER is the user who calls the procedure. DEFINER is the creator of the procedure. The default value is DEFINER.
• Comment : For documentation purposes; the default value is ""

Calling a Stored Procedure

To call a procedure, you only need to enter the word CALL, followed by the name of the procedure, and then the parentheses, including all the parameters between them (variables or values). Parentheses are compulsory.

CALL stored_procedure_name (param1, param2, ....)

CALL procedure1(10 , 'string parameter' , @parameter_var);

Modify a Stored Procedure

MySQL provides an ALTER PROCEDURE statement to modify a routine, but only allows for the ability to change certain characteristics. If you need to alter the body or the parameters, you must drop and recreate the procedure.

Delete a Stored Procedure

DROP PROCEDURE IF EXISTS p2;

This is a simple command. The IF EXISTS clause prevents an error in case the procedure does not exist.

Parameters in Stored Procedure

Let’s examine how you can define parameters within a stored procedure.

• CREATE PROCEDURE proc1 () : Parameter list is empty
• CREATE PROCEDURE proc1 (IN varname DATA-TYPE) : One input parameter. The word IN is optional because parameters are IN (input) by default.
• CREATE PROCEDURE proc1 (OUT varname DATA-TYPE) : One output parameter.
• CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE) : One parameter which is both input and output.

We can define multiple parameters defined with different types.

IN Parameter Example

 DELIMITER //
 
CREATE PROCEDURE `proc_IN` (IN var1 INT)
BEGIN
    SELECT var1 + 2 AS result;
END//

OUT Parameter Example

DELIMITER //
 
CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100))
BEGIN
    SET var1 = 'This is a test';
END //

INOUT Parameter Example

DELIMITER //
 
CREATE PROCEDURE `proc_INOUT` (OUT var1 INT)
BEGIN
    SET var1 = var1 * 2;
END //

Benefits of using stored procedures

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. A stored procedure preserves data integrity because information is entered in a consistent manner. It improves productivity because statements in a stored procedure only must be written once.

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.

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 being sent individually.

Drawback of stored procedures

There are some disadvantages to using stored procedures and they are portability, testing and version control.

Portability: Stored procedures are written in vendor specific languages and that makes it hard to transfer them from one installation, such as Oracle, to another like SQL Server.

Testing: Testing and debugging stored procedures can be tricky. It can be more difficult to put together the debugging tools to allow you to step through and trace stored procedures.

Version Control: It is very hard to keep track of changes made to stored procedures than it is native source code. Unlike most native code IDE’s, there are no any ways to integrate source code control into the stored procedure development tool. Because of this, most version control activities are limited to extracting the stored procedure as a CREATE PROCEDURE script and then manually importing into a version control system.