SQL - Stored Procedures

SQL Stored Procedures: A Modern Guide

What is a Stored Procedure?

A stored procedure is a prepared SQL code block that you can save, so the code can be reused over and over again. It’s like a function in a programming language, but it lives inside your database.

You can pass parameters to a stored procedure, so that it can act on the data you provide. They are powerful tools for encapsulating business logic, performing complex database operations, and ensuring data consistency.

Note: While similar to User-Defined Functions (UDFs), stored procedures are more flexible. They can perform Data Manipulation Language (DML) operations (INSERT, UPDATE, DELETE) and don’t necessarily have to return a value, whereas functions are typically designed to compute and return a single value and have limitations on side effects.

Basic Syntax (MySQL/MariaDB)

The syntax for creating procedures varies between database systems. This tutorial will use MySQL/MariaDB syntax. The core concepts are transferable to other systems like PostgreSQL (using PL/pgSQL) or SQL Server (using T-SQL).

DELIMITER //

CREATE PROCEDURE ProcedureName(parameter_mode parameter_name DATATYPE)
BEGIN
    -- Your SQL statements go here
END //

DELIMITER ;

• DELIMITER //: This command changes the standard delimiter (;) to (//) so that the database client knows where the entire procedure definition ends. It’s a client-side command, not part of the SQL procedure syntax itself.
• CREATE PROCEDURE: The keyword to start defining a new procedure.
• BEGIN…END: This block contains the body of the procedure, including all your SQL logic.
• parameter_mode: Can be IN, OUT, or INOUT, which we’ll explore below.

Creating Your First Procedure (with IN Parameters)

Let’s start with a practical example. We’ll use a Customers table to fetch customer details based on their age. The IN parameter is the default mode and is used to pass a value into the procedure.

Example: Setup the Data

First, let’s create and populate our Customers table with modern data types.

CREATE TABLE Customers (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Age INT NOT NULL,
    City VARCHAR(100),
    Salary DECIMAL(10, 2)
);

INSERT INTO Customers (Name, Age, City, Salary)
VALUES
    ('Ramesh', 32, 'Ahmedabad', 2000.00),
    ('Khilan', 25, 'Delhi', 1500.00),
    ('Kaushik', 23, 'Kota', 2000.00),
    ('Chaitali', 25, 'Mumbai', 6500.00),
    ('Hardik', 27, 'Bhopal', 8500.00),
    ('Komal', 22, 'Hyderabad', 4500.00),
    ('Muffy', 24, 'Indore', 10000.00);

Example: Create the Procedure

This procedure, GetCustomersByAge, accepts an integer (targetAge) and returns all customers of that age.

DELIMITER //

CREATE PROCEDURE GetCustomersByAge(IN targetAge INT)
BEGIN
    -- Selects customers matching the input age
    SELECT ID, Name, Age, City, Salary
    FROM Customers
    WHERE Age = targetAge;
END //

DELIMITER ;

How to Use It

You execute a procedure using the CALL statement.

CALL GetCustomersByAge(25);

This call will return a result set containing the two customers who are 25 years old.

ID	Name	Age	City	Salary
2	Khilan	25	Delhi	1500.00
4	Chaitali	25	Mumbai	6500.00

Returning Data with OUT Parameters

An OUT parameter is used to return a value from the procedure back to the caller. This is useful when you need to return a single value, like a calculated count or a status message, instead of a full result set.

Example: Count Customers in a City

This procedure counts how many customers are in a given city and returns the count via an OUT parameter.

DELIMITER //

CREATE PROCEDURE CountCustomersInCity(
    IN targetCity VARCHAR(100),
    OUT customerCount INT
)
BEGIN
    SELECT COUNT(*)
    INTO customerCount
    FROM Customers
    WHERE City = targetCity;
END //

DELIMITER ;

How to Use It

When calling a procedure with an OUT parameter, you must provide a session variable (prefixed with @) to receive the returned value.

-- Call the procedure, passing a variable to hold the output
CALL CountCustomersInCity('Mumbai', @mumbaiCount);

-- Select the variable to see the result
SELECT @mumbaiCount AS NumberOfCustomersInMumbai;

Output

NumberOfCustomersInMumbai
1

Modifying Data with INOUT Parameters

An INOUT parameter is a combination of IN and OUT. It allows you to pass a value into the procedure, modify it inside the procedure, and have the new value available outside the procedure.

Example: Apply a Salary Bonus

This procedure takes a customer ID and a bonus percentage. It calculates the new salary and updates it in the table. It also updates the input salary variable to reflect the new salary.

DELIMITER //

CREATE PROCEDURE ApplyBonus(
    IN customerId INT,
    INOUT salary DECIMAL(10, 2),
    IN bonusPercentage DECIMAL(5, 2)
)
BEGIN
    -- Calculate the new salary
    DECLARE newSalary DECIMAL(10, 2);
    SET newSalary = salary * (1 + (bonusPercentage / 100.0));

    -- Update the table
    UPDATE Customers
    SET Salary = newSalary
    WHERE ID = customerId;

    -- Update the INOUT parameter to the new value
    SET salary = newSalary;
END //

DELIMITER ;

How to Use It

-- First, get the current salary for customer 1 into a variable
SELECT Salary INTO @currentSalary FROM Customers WHERE ID = 1;

-- Call the procedure to apply a 10% bonus
CALL ApplyBonus(1, @currentSalary, 10.0);

-- Check the updated value in the variable
SELECT @currentSalary AS NewSalary;

The original salary of customer 1 was 2000.00. After a 10% bonus, the new salary is 2200.00.

NewSalary
2200.00

Essential Best Practices

• Naming Conventions: Use a consistent prefix, like usp_ (user stored procedure) or a verb-noun format (e.g., GetCustomers, UpdateSalary).
• Single Responsibility: Each procedure should do one thing well. Avoid creating monolithic procedures that handle dozens of different cases.
• Commenting: Document complex logic, parameter usage, and the purpose of the procedure directly in the code.
• Use Schemas: In larger databases, organize procedures into schemas to group related logic (e.g., sales.UpdateOrder, reporting.GenerateDailySummary).
• Version Control: Store your procedure definitions in a version control system like Git. Use database migration tools (e.g., Flyway, Liquibase) to manage and deploy changes systematically.

Error Handling and Transactions

Real-world procedures must be robust. What happens if an UPDATE fails? You should wrap your logic in a transaction and include error handling to ensure data integrity.

DELIMITER //

CREATE PROCEDURE TransferFunds(
    IN fromAccountId INT,
    IN toAccountId INT,
    IN amount DECIMAL(10, 2)
)
BEGIN
    -- Exit if any error occurs
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- Roll back the transaction and signal an error
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Debit from the first account
    UPDATE Accounts SET balance = balance - amount WHERE ID = fromAccountId;

    -- Credit to the second account
    UPDATE Accounts SET balance = balance + amount WHERE ID = toAccountId;

    COMMIT;
END //

DELIMITER ;

In this example, if either the debit or the credit UPDATE fails, the EXIT HANDLER catches the exception, the ROLLBACK undoes any changes, and the procedure stops, preventing a partial, incorrect transaction.

Security: Preventing SQL Injection

One of the key security benefits of stored procedures is that they help prevent SQL injection attacks. By using parameters (like IN targetAge INT), the database engine treats the input as data, not as executable code. This is the correct way to use them.

NEVER build dynamic SQL by concatenating strings with user input. This is extremely dangerous.

-- DANGEROUS - DO NOT DO THIS!
SET @sql = CONCAT('SELECT * FROM Customers WHERE Age = ', userInput);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Always use the built-in parameter functionality as shown in our first examples. It is inherently safer.

Advantages and Disadvantages

Advantages

• Improved Performance: Procedures are pre-compiled and cached by the database, often leading to faster execution than ad-hoc queries, especially for complex logic.
• Reduced Network Traffic: Instead of sending long, complex queries over the network, an application can just send CALL MyProcedure(1, 'A'), and the logic executes on the server.
• Enhanced Security: You can grant users EXECUTE permissions on a procedure without giving them direct access to the underlying tables, enforcing a strict API for data access.
• Code Reusability and Consistency: Encapsulating business logic in one place ensures it’s applied consistently everywhere it’s needed and simplifies maintenance.

Disadvantages

• Vendor Lock-In: Procedure syntax is highly specific to the database system (T-SQL vs. PL/pgSQL vs. MySQL’s syntax), making migration to another database difficult.
• Debugging Challenges: Debugging stored procedures can be more complex than debugging application code, often requiring specialized database tools.
• Increased Server Load: Heavy logic in procedures can increase the CPU load on the database server, which might be a bottleneck in some architectures.
• Brittleness: A change in the database schema (e.g., renaming a column) can break procedures, and these dependencies are sometimes hard to track.