What Is a Stored Procedure and How Does It Work?

1. Overview

Stored procedures stand as powerful tools that help achieve efficiency and security in database management. Moreover, they offer a host of additional benefits that can significantly enhance database interactions.

In this tutorial, we explore stored procedures and how to use them in database management.

All examples are based on a sample schema.

2. Stored Procedures

At its core, a stored procedure is a precompiled collection of one or more SQL statements saved within the database. We can think of it as a function for a database: we define it once, and then we can call it whenever we need, passing parameters if necessary.

Stored procedures have several important features that distinguish them from simple queries:

• they are precompiled, which means the database compiles and optimizes them at the time of creation
• they can be parameterized
• their reusability makes them an invaluable tool for maintaining consistent and efficient database operations

In comparison with ad-hoc queries, stored procedures offer distinct advantages. Unlike ad-hoc queries, which are compiled each time they run, stored procedures are compiled once and stored in the database. This precompilation step can lead to significant performance improvements. Moreover, the database can cache execution plans for stored procedures, further enhancing their efficiency.

3. Syntax for Creating a Stored Procedure

The syntax for creating stored procedures varies slightly between different database management systems.

3.1. MySQL

In MySQL, creating a stored procedure involves changing the delimiter and using the CREATE PROCEDURE statement:

DELIMITER //
CREATE PROCEDURE procedure_name (IN parameter1 datatype, OUT parameter2 datatype)
BEGIN
    -- SQL statements
    DECLARE variable_name datatype;
    SET variable_name = some_value;
    -- Other statements, e.g., SELECT, INSERT, UPDATE, DELETE
    SELECT column1, column2 INTO parameter2 FROM table_name WHERE condition;
END //
DELIMITER ;

MySQL procedures can contain multiple SQL statements and use local variables we declare with DECLARE.

3.2. PostgreSQL

Similarly, PostgreSQL uses the CREATE PROCEDURE statement to create a procedure:

CREATE OR REPLACE PROCEDURE procedure_name(
    IN parameter1 datatype,
    IN parameter2 datatype,
    OUT parameter3 datatype
)
LANGUAGE plpgsql
AS $$
DECLARE
    variable_name datatype;
BEGIN
    -- SQL statements
    variable_name := some_value;
    -- Other statements, e.g., SELECT, INSERT, UPDATE, DELETE
    SELECT column1, column2 INTO parameter3 FROM table_name WHERE condition;
END;
$$;

Here, LANGUAGE plpgsql indicates that the procedure body is written in PL (pgSQL). We further enclose the procedure body in  $$ delimiters, which serve as quote marks for the code.

3.3. MS SQL Server

MS SQL Server uses a syntax similar to MySQL but with some key differences:

CREATE PROCEDURE procedure_name
    @parameter1 datatype,
    @parameter2 datatype OUTPUT
AS
BEGIN
    -- SQL statements
    DECLARE @variable_name datatype;
    SET @variable_name = some_value;
    -- Other statements, e.g., SELECT, INSERT, UPDATE, DELETE
    SELECT @parameter2 = column1 FROM table_name WHERE condition;
END;

In MS SQL Server, we prefix parameters and variables with @ and specify the output parameters with the OUTPUT keyword.

4. Parameters in Stored Procedures

Parameters enable stored procedures to be flexible and reusable. They support the passing of data into procedures and retrieving results, much like function arguments and return values in traditional programming.

Next, let’s look at how different database systems handle parameters in stored procedures.

4.1. MySQL

MySQL supports three types of parameters:

• IN: input parameters
• OUT: output parameters
• INOUT: parameters for both input and output

To demonstrate the use of parameters, let’s examine a procedure that calculates the average GPA for a given department:

DELIMITER //

CREATE PROCEDURE CalculateAvgGPA(
    IN dept_id INT,
    OUT avg_gpa FLOAT
)
BEGIN
    SELECT AVG(s.gpa) INTO avg_gpa
    FROM Student s
    JOIN Registration r ON s.id = r.student_id
    JOIN Course c ON r.course_id = c.id
    WHERE c.department_id = dept_id;
END//

DELIMITER;

In this example, dept_id serves as an input parameter, while avg_gpa is an output parameter that holds the calculated average GPA.

4.2. PostgreSQL

PostgreSQL also supports the three types of parameters that MySQL supports.

Let’s demonstrate the IN and OUT parameters:

CREATE OR REPLACE PROCEDURE CalculateAvgGPA(
    dept_id INT,
    OUT avg_gpa FLOAT
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT AVG(s.gpa) INTO avg_gpa
    FROM Student s
    JOIN Registration r ON s.id = r.student_id
    JOIN Course c ON r.course_id = c.id
    WHERE c.department_id = dept_id;
END;
$$;

However, unlike MySQL, PostgreSQL doesn’t require explicit declaration of IN parameters – they are IN by default.

4.3. MS SQL Server

MS SQL Server uses the @ symbol for parameters and the OUTPUT keyword for output parameters:

CREATE PROCEDURE CalculateAvgGPA
    @dept_id INT,
    @avg_gpa FLOAT OUTPUT
AS
BEGIN
    SELECT @avg_gpa = AVG(s.gpa)
    FROM Student s
    JOIN Registration r ON s.id = r.student_id
    JOIN Course c ON r.course_id = c.id
    WHERE c.department_id = @dept_id;
END;

In MS SQL Server, output parameters are explicitly marked with OUTPUT in the parameter list.

5. Calling and Executing Stored Procedures

Once we’ve created a stored procedure, the next step is usually to call and execute it. The method to do this also varies between database systems, so let’s explore each one.

5.1. MySQL

In MySQL, we use the CALL statement to execute a stored procedure:

mysql> SET @dept_id = 1;
mysql> SET @result = 0;
mysql> CALL CalculateAvgGPA(@dept_id, @result);
mysql> SELECT @result AS average_gpa;
+--------------------+
| average_gpa        |
+--------------------+
| 4.0725860595703125 |
+--------------------+
1 row in set (0.00 sec)

This example sets up variables, calls the procedure, and then retrieves the result.

5.2. PostgreSQL

PostgreSQL uses a slightly different approach, usually involving a DO block:

postgres=> DO $$ 
postgres$> DECLARE 
postgres$>     dept_id INT := 1; 
postgres$>     avg_gpa FLOAT; 
postgres$> BEGIN 
postgres$>     CALL CalculateAvgGPA(dept_id, avg_gpa); 
postgres$>     RAISE NOTICE 'Average GPA: %', avg_gpa; 
postgres$> END $$;
NOTICE:  Average GPA: 4.0725860595703125
DO

This method facilitates variable declaration and result handling within a single block.

5.3. MS SQL Server

MS SQL Server uses the EXEC or EXECUTE statement to call stored procedures:

DECLARE @dept_id INT = 1;
DECLARE @avg_gpa FLOAT;

EXEC CalculateAvgGPA @dept_id, @avg_gpa OUTPUT;

SELECT @avg_gpa AS average_gpa;
average_gpa
--------------------
4.0725860595703125

(1 row affected)

This approach declares variables, executes the procedure, and then selects the output for display.

In general, we mainly focus on MySQL for the examples, but each one can be ported fairly easily via the rules and syntax discussed thus far.

6. Benefits of Using Stored Procedures

The advantages of incorporating stored procedures into the database management strategy are numerous and significant.

6.1. Performance Improvements

One of the most compelling reasons to use stored procedures is the potential for significant performance improvements. Since stored procedures are precompiled, they often execute faster than equivalent ad-hoc queries. Furthermore, database systems can cache execution plans for stored procedures, leading to even faster subsequent executions.

6.2. Enhanced Security

Security is a paramount concern in database management, and stored procedures can play an important role in enhancing it. By using stored procedures, we can implement a principle of least privilege, granting users access only to specific procedures rather than directly to tables. This approach provides an additional layer of abstraction and control over data access.

For instance, we can grant execution rights to a specific procedure without giving direct table access:

mysql> GRANT EXECUTE ON PROCEDURE University.CalculateAvgGPA TO 'root'@'localhost';

This SQL statement enables root to execute the CalculateAvgGPA procedure without necessarily having direct access to the underlying tables.

6.3. Code Reusability and Maintainability

Stored procedures excel in promoting code reuse and maintaining consistency across database operations. Once created, we can call a procedure from multiple places in an application. Thus, we can apply the same logic consistently. This centralization of logic not only reduces code duplication but also simplifies maintenance – if a change is needed, it only needs to be implemented in one place.

7. Working With Stored Procedures

Other database operations useful for stored procedures include handling various parameters, implementing complex logic control structures, and managing errors to ensure robust procedure execution.

7.1. Handling Input and Output Parameters

We’ve already seen examples of input and output parameters, but let’s explore a case where we use an INOUT parameter. This parameter type serves as both input and output, enabling the modification of its value within the procedure.

Let’s consider a simple procedure that increments a given value:

DELIMITER //

CREATE PROCEDURE IncrementAndReturn (
    INOUT value INT
)
BEGIN
    SET value = value + 1;
END //

DELIMITER ;

Then, we can call this procedure and see the result:

mysql> SET @my_value = 5;
mysql> CALL IncrementAndReturn(@my_value);
mysql> SELECT @my_value;
+-----------+
| @my_value |
+-----------+
|         6 |
+-----------+
1 row in set (0.00 sec)

Thus, we use value to demonstrate how we can use an INOUT parameter to both pass in a value and return a modified result.

7.2. Using Control Structures

Stored procedures aren’t limited to simple SQL statements; they can incorporate complex logic using control structures like IF-ELSE statements and loops. These structures create more sophisticated database operations that can make decisions and iterate over data.

Now, let’s create a procedure that assigns a letter grade based on a numeric grade:

DELIMITER //

CREATE PROCEDURE AssignLetterGrade(
    IN numeric_grade FLOAT,
    OUT letter_grade CHAR(2)
)
BEGIN
    IF numeric_grade >= 90 THEN
        SET letter_grade = 'A';
    ELSEIF numeric_grade >= 80 THEN
        SET letter_grade = 'B';
    ELSEIF numeric_grade >= 70 THEN
        SET letter_grade = 'C';
    ELSEIF numeric_grade >= 60 THEN
        SET letter_grade = 'D';
    ELSE
        SET letter_grade = 'F';
    END IF;
END //

DELIMITER ;

Then, we can look at an example of running this procedure:

mysql> SET @input_grade = 85;
mysql> SET @output_grade = '';
mysql> CALL AssignLetterGrade(@input_grade, @output_grade);
mysql> SELECT @output_grade;
+---------------+
| @output_grade |
+---------------+
| B             |
+---------------+
1 row in set (0.00 sec)

This procedure showcases the use of IF-ELSEIF-ELSE control structure within a stored procedure.

8. Dynamic SQL Within Stored Procedures

While stored procedures typically contain static SQL statements, we can also use dynamic SQL. This enables the construction and execution of SQL statements on the fly within the stored procedures.

To demonstrate, let’s see an example of a procedure that uses dynamic SQL:

DELIMITER //

CREATE PROCEDURE DynamicCourseQuery(
    IN where_clause VARCHAR(1000)
)
BEGIN
    SET @sql = CONCAT('SELECT id, name, credits FROM Course WHERE ', where_clause);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

We could then call this procedure with different conditions:

mysql> CALL DynamicCourseQuery('credits > 3 AND department_id = 2');
+-------+------------------------------------------+---------+
| id    | name                                     | credits |
+-------+------------------------------------------+---------+
| EC111 | Principles of Communication              |       7 |
| EC121 | Signal and Systems                       |       7 |
| EC131 | Electronics-I                            |       7 |
| EC141 | Modeling Tools for Electronics Engineers |       7 |
| EC151 | Statistics for Electronics Engineers     |       7 |
| EC211 | Principles of Communication-II           |       7 |
| EC221 | Digital Signal Processing                |       7 |
| EC222 | Introduction to Programming Matlab       |       7 |
| EC231 | Electronics-II                           |       7 |
| EC411 | Principles of Communication-III          |       5 |
| EC421 | Advanced Signal and Systems              |       5 |
| EC431 | Electronics-III                          |       5 |
+-------+------------------------------------------+---------+
12 rows in set (0.00 sec)

Query OK, 0 rows affected (0.06 sec)

However, the flexibility of dynamic query comes with a caveat: dynamic SQL can be vulnerable to SQL injection if we’re not handling it properly. Hence, we should always validate and sanitize any user input used in dynamic SQL to prevent such problems.

9. Conclusion

In this article, we saw how stored procedures can simplify common tasks in database management.

It’s clear that these database objects offer a powerful set of tools for database management and application development. From performance improvements to code reusability, stored procedures provide numerous benefits that can significantly enhance database operations.