Eric Campbell 5 min read SQL

SQL - Stored Procedures Tutorial

Stored procedures are precompiled SQL statements stored in the database that execute as a single unit. Unlike ad-hoc queries sent from applications, stored procedures reside on the database server...

Key Insights

  • Stored procedures encapsulate SQL logic on the database server, reducing network traffic and enabling code reuse across applications while maintaining centralized business rules
  • Parameters (IN, OUT, INOUT) and control flow structures (IF/ELSE, WHILE, CASE) transform stored procedures into powerful programming constructs that handle complex business logic
  • Transaction management within stored procedures ensures data integrity through atomic operations with proper error handling and rollback capabilities

What Are Stored Procedures

Stored procedures are precompiled SQL statements stored in the database that execute as a single unit. Unlike ad-hoc queries sent from applications, stored procedures reside on the database server and are called by name with parameters.

-- Basic stored procedure syntax (MySQL)
DELIMITER //
CREATE PROCEDURE GetEmployeeCount()
BEGIN
    SELECT COUNT(*) AS total_employees FROM employees;
END //
DELIMITER ;

-- Execute the procedure
CALL GetEmployeeCount();

The key advantage: compile once, execute many times. The database optimizes the execution plan and caches it, improving performance for repeated operations.

Working with Parameters

Parameters enable dynamic behavior. Most databases support three parameter types: IN (input), OUT (output), and INOUT (both).

-- IN parameter example
DELIMITER //
CREATE PROCEDURE GetEmployeesByDepartment(IN dept_id INT)
BEGIN
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department_id = dept_id
    ORDER BY last_name;
END //
DELIMITER ;

CALL GetEmployeesByDepartment(5);

-- OUT parameter example
DELIMITER //
CREATE PROCEDURE GetDepartmentStats(
    IN dept_id INT,
    OUT emp_count INT,
    OUT avg_salary DECIMAL(10,2)
)
BEGIN
    SELECT COUNT(*), AVG(salary)
    INTO emp_count, avg_salary
    FROM employees
    WHERE department_id = dept_id;
END //
DELIMITER ;

-- Call with output variables
CALL GetDepartmentStats(5, @count, @avg_sal);
SELECT @count, @avg_sal;

-- INOUT parameter example
DELIMITER //
CREATE PROCEDURE ApplyBonus(INOUT salary DECIMAL(10,2), IN bonus_pct DECIMAL(5,2))
BEGIN
    SET salary = salary * (1 + bonus_pct / 100);
END //
DELIMITER ;

SET @emp_salary = 50000;
CALL ApplyBonus(@emp_salary, 10);
SELECT @emp_salary; -- Returns 55000

Control Flow and Logic

Stored procedures support conditional logic and loops, enabling complex business rules.

-- IF/ELSE statements
DELIMITER //
CREATE PROCEDURE CalculateBonus(
    IN emp_id INT,
    OUT bonus_amount DECIMAL(10,2)
)
BEGIN
    DECLARE emp_salary DECIMAL(10,2);
    DECLARE years_service INT;
    
    SELECT salary, YEAR(CURDATE()) - YEAR(hire_date)
    INTO emp_salary, years_service
    FROM employees
    WHERE employee_id = emp_id;
    
    IF years_service >= 10 THEN
        SET bonus_amount = emp_salary * 0.15;
    ELSEIF years_service >= 5 THEN
        SET bonus_amount = emp_salary * 0.10;
    ELSE
        SET bonus_amount = emp_salary * 0.05;
    END IF;
END //
DELIMITER ;

-- CASE statements for multiple conditions
DELIMITER //
CREATE PROCEDURE ClassifyEmployee(IN emp_id INT, OUT classification VARCHAR(20))
BEGIN
    DECLARE emp_salary DECIMAL(10,2);
    
    SELECT salary INTO emp_salary
    FROM employees
    WHERE employee_id = emp_id;
    
    SET classification = CASE
        WHEN emp_salary >= 100000 THEN 'Senior'
        WHEN emp_salary >= 70000 THEN 'Mid-Level'
        WHEN emp_salary >= 40000 THEN 'Junior'
        ELSE 'Entry-Level'
    END;
END //
DELIMITER ;

-- WHILE loops for iterative processing
DELIMITER //
CREATE PROCEDURE GenerateSequence(IN max_num INT)
BEGIN
    DECLARE counter INT DEFAULT 1;
    
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_sequence (num INT);
    DELETE FROM temp_sequence;
    
    WHILE counter <= max_num DO
        INSERT INTO temp_sequence VALUES (counter);
        SET counter = counter + 1;
    END WHILE;
    
    SELECT * FROM temp_sequence;
END //
DELIMITER ;

Error Handling and Transactions

Production stored procedures require robust error handling to maintain data integrity.

-- Basic transaction with error handling
DELIMITER //
CREATE PROCEDURE TransferFunds(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2),
    OUT status_msg VARCHAR(100)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET status_msg = 'Transaction failed - rolled back';
    END;
    
    START TRANSACTION;
    
    -- Deduct from source account
    UPDATE accounts 
    SET balance = balance - amount 
    WHERE account_id = from_account AND balance >= amount;
    
    -- Check if update affected a row
    IF ROW_COUNT() = 0 THEN
        ROLLBACK;
        SET status_msg = 'Insufficient funds';
    ELSE
        -- Add to destination account
        UPDATE accounts 
        SET balance = balance + amount 
        WHERE account_id = to_account;
        
        COMMIT;
        SET status_msg = 'Transfer successful';
    END IF;
END //
DELIMITER ;

-- Detailed error handling with multiple conditions
DELIMITER //
CREATE PROCEDURE ProcessOrder(
    IN customer_id INT,
    IN product_id INT,
    IN quantity INT,
    OUT order_id INT,
    OUT error_code INT
)
BEGIN
    DECLARE available_stock INT;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error_code = -1;
    
    SET error_code = 0;
    START TRANSACTION;
    
    -- Check stock availability
    SELECT stock_quantity INTO available_stock
    FROM products
    WHERE product_id = product_id
    FOR UPDATE; -- Lock the row
    
    IF available_stock < quantity THEN
        SET error_code = 1; -- Insufficient stock
        ROLLBACK;
    ELSE
        -- Create order
        INSERT INTO orders (customer_id, order_date, status)
        VALUES (customer_id, NOW(), 'PENDING');
        
        SET order_id = LAST_INSERT_ID();
        
        -- Add order items
        INSERT INTO order_items (order_id, product_id, quantity)
        VALUES (order_id, product_id, quantity);
        
        -- Update stock
        UPDATE products
        SET stock_quantity = stock_quantity - quantity
        WHERE product_id = product_id;
        
        COMMIT;
    END IF;
END //
DELIMITER ;

Cursors for Row-by-Row Processing

When set-based operations aren’t sufficient, cursors enable row-by-row processing.

DELIMITER //
CREATE PROCEDURE CalculateDepartmentBudgets()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE dept_id INT;
    DECLARE dept_budget DECIMAL(12,2);
    
    DECLARE dept_cursor CURSOR FOR
        SELECT department_id, SUM(salary) * 1.3 -- Salary + 30% overhead
        FROM employees
        GROUP BY department_id;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    CREATE TEMPORARY TABLE IF NOT EXISTS dept_budgets (
        department_id INT,
        annual_budget DECIMAL(12,2)
    );
    
    DELETE FROM dept_budgets;
    
    OPEN dept_cursor;
    
    read_loop: LOOP
        FETCH dept_cursor INTO dept_id, dept_budget;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        INSERT INTO dept_budgets VALUES (dept_id, dept_budget);
    END LOOP;
    
    CLOSE dept_cursor;
    
    SELECT * FROM dept_budgets ORDER BY annual_budget DESC;
END //
DELIMITER ;

Dynamic SQL Execution

Some scenarios require building SQL statements at runtime.

DELIMITER //
CREATE PROCEDURE GetTableData(IN table_name VARCHAR(64))
BEGIN
    SET @sql = CONCAT('SELECT * FROM ', table_name, ' LIMIT 100');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- With parameters
DELIMITER //
CREATE PROCEDURE FilterByColumn(
    IN table_name VARCHAR(64),
    IN column_name VARCHAR(64),
    IN filter_value VARCHAR(100)
)
BEGIN
    SET @sql = CONCAT(
        'SELECT * FROM ', table_name,
        ' WHERE ', column_name, ' = ?'
    );
    PREPARE stmt FROM @sql;
    SET @value = filter_value;
    EXECUTE stmt USING @value;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

Performance Considerations

Stored procedures impact performance in several ways. They reduce network roundtrips by executing multiple statements server-side. Query plans are cached after first execution. However, parameter sniffing can cause suboptimal plans when parameter values vary significantly.

-- Recompile hint for parameter-sensitive queries (SQL Server)
CREATE PROCEDURE GetOrdersByDateRange
    @StartDate DATE,
    @EndDate DATE
WITH RECOMPILE
AS
BEGIN
    SELECT * FROM orders
    WHERE order_date BETWEEN @StartDate AND @EndDate;
END;

Monitor execution plans and consider splitting procedures when a single procedure serves vastly different use cases. Use appropriate indexing on tables accessed by stored procedures.

Security and Permissions

Stored procedures provide a security layer. Grant EXECUTE permission without giving direct table access.

-- SQL Server example
CREATE PROCEDURE GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SELECT * FROM orders WHERE customer_id = @CustomerId;
END;

-- Grant execution only
GRANT EXECUTE ON GetCustomerOrders TO AppUser;
-- No direct table permissions needed

This prevents SQL injection when properly parameterized and limits what operations applications can perform. Stored procedures enforce business rules at the database level, ensuring consistency regardless of which application accesses the data.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.