SQL Stored Procedures: Complete Guide with Diagrams
Comprehensive guide to SQL stored procedures covering parameters, variables, control flow, error handling, cursors, transactions, and best practices with visual diagrams
SQL Stored Procedures
Table of Contents
- Introduction to Stored Procedures
- Creating Procedures
- Parameters (IN, OUT, INOUT)
- Variables and Data Types
- Control Flow Statements
- Error Handling
- Cursors
- Dynamic SQL
- Transaction Control
- Best Practices
Introduction to Stored Procedures
A Stored Procedure is a prepared SQL code that you can save and reuse. It's stored in the database and can accept parameters, perform operations, and return results.
Benefits
┌──────────────────────────────────────────────────────────┐
│ STORED PROCEDURE BENEFITS │
├──────────────────────────────────────────────────────────┤
│ │
│ ✅ Performance │
│ - Pre-compiled and cached │
│ - Reduced network traffic │
│ - Execution plan reuse │
│ │
│ ✅ Security │
│ - Controlled data access │
│ - Parameter validation │
│ - Hide complex logic │
│ │
│ ✅ Maintainability │
│ - Centralized business logic │
│ - Easier updates │
│ - Code reusability │
│ │
│ ✅ Consistency │
│ - Standardized operations │
│ - Reduced errors │
│ - Enforced business rules │
│ │
└──────────────────────────────────────────────────────────┘
Architecture
┌─────────────────────────────────────────────────────────┐
│ STORED PROCEDURE ARCHITECTURE │
├─────────────────────────────────────────────────────────┤
│ │
│ Application Layer: │
│ ┌──────────────────┐ │
│ │ CALL procedure │ │
│ │ (param1, ...) │ │
│ └────────┬─────────┘ │
│ │ │
│ ▼ │
│ Database Layer: │
│ ┌──────────────────┐ │
│ │ Stored Procedure │ │
│ │ - Parameters │ │
│ │ - Variables │ │
│ │ - Logic │ │
│ │ - SQL Queries │ │
│ └────────┬─────────┘ │
│ │ │
│ ▼ │
│ ┌──────────────────┐ │
│ │ Return Results │ │
│ └──────────────────┘ │
│ │
└─────────────────────────────────────────────────────────┘
Stored Procedure Execution Flow:
┌─────────────────────────────────────────────────────────────┐
│ COMPLETE STORED PROCEDURE EXECUTION FLOW │
├─────────────────────────────────────────────────────────────┤
│ │
│ 1. CALL/EXECUTE Procedure │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ Application/Client │ │
│ │ ┌──────────────────────────────────┐ │ │
│ │ │ CALL procedure_name(params) │ │ │
│ │ └──────────────┬───────────────────┘ │ │
│ └────────────────┼───────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ 2. Database Engine Processing │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ ┌──────────────────────────────────┐ │ │
│ │ │ Parse & Validate │ │ │
│ │ │ - Check syntax │ │ │
│ │ │ - Verify parameters │ │ │
│ │ │ - Check permissions │ │ │
│ │ └──────────────┬───────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌──────────────────────────────────┐ │ │
│ │ │ Compile (if not cached) │ │ │
│ │ │ - Generate execution plan │ │ │
│ │ │ - Optimize queries │ │ │
│ │ │ - Cache plan │ │ │
│ │ └──────────────┬───────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌──────────────────────────────────┐ │ │
│ │ │ Execute Procedure Body │ │ │
│ │ │ │ │ │
│ │ │ ┌────────────────────────────┐ │ │ │
│ │ │ │ Declare Variables │ │ │ │
│ │ │ └────────┬───────────────────┘ │ │ │
│ │ │ │ │ │ │
│ │ │ ▼ │ │ │
│ │ │ ┌────────────────────────────┐ │ │ │
│ │ │ │ Initialize Parameters │ │ │ │
│ │ │ └────────┬───────────────────┘ │ │ │
│ │ │ │ │ │ │
│ │ │ ▼ │ │ │
│ │ │ ┌────────────────────────────┐ │ │ │
│ │ │ │ Execute SQL Statements │ │ │ │
│ │ │ │ - SELECT │ │ │ │
│ │ │ │ - INSERT │ │ │ │
│ │ │ │ - UPDATE │ │ │ │
│ │ │ │ - DELETE │ │ │ │
│ │ │ │ - Control Flow (IF/LOOP) │ │ │ │
│ │ │ └────────┬───────────────────┘ │ │ │
│ │ │ │ │ │ │
│ │ │ ▼ │ │ │
│ │ │ ┌─────────┐ │ │ │
│ │ │ │ Error? │ │ │ │
│ │ │ └──┬───┬──┘ │ │ │
│ │ │ YES │ │ NO │ │ │
│ │ │ ▼ ▼ │ │ │
│ │ │ ┌────────┐ ┌──────────────┐ │ │ │
│ │ │ │Handler │ │ Continue │ │ │ │
│ │ │ │Execute │ │ Execution │ │ │ │
│ │ │ └────────┘ └──────────────┘ │ │ │
│ │ │ │ │ │
│ │ └──────────────────────────────────┘ │ │
│ └──────────────────┬───────────────────────────────────┘ │
│ │ │
│ ▼ │
│ 3. Return Results │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ ┌──────────────────────────────────┐ │ │
│ │ │ Process Output Parameters │ │ │
│ │ │ - Set OUT values │ │ │
│ │ │ - Prepare result sets │ │ │
│ │ └──────────────┬───────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌──────────────────────────────────┐ │ │
│ │ │ Return to Caller │ │ │
│ │ │ - Result sets │ │ │
│ │ │ - Output parameters │ │ │
│ │ │ - Return code │ │ │
│ │ └──────────────┬───────────────────┘ │ │
│ └────────────────┼───────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ Application/Client receives results │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Performance Optimization: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ ⚡ Execution Plan Cache: │ │
│ │ First Call: Parse → Compile → Execute │ │
│ │ Next Calls: [Cached Plan] → Execute │ │
│ │ │ │
│ │ 📊 Statistics: │ │
│ │ - Execution count │ │
│ │ - Average duration │ │
│ │ - Resource usage │ │
│ │ │ │
│ │ 🔒 Locking: │ │
│ │ - Shared locks (SELECT) │ │
│ │ - Exclusive locks (INSERT/UPDATE/DELETE) │ │
│ │ - Released on completion │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
Sample Tables
-- Create sample tables
CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
phone VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
status VARCHAR(20) DEFAULT 'Pending',
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
CREATE TABLE order_items (
order_item_id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT NOT NULL,
product_name VARCHAR(100),
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(order_id)
);
CREATE TABLE audit_log (
log_id INT PRIMARY KEY AUTO_INCREMENT,
action VARCHAR(50),
table_name VARCHAR(50),
record_id INT,
user_name VARCHAR(50),
log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Creating Procedures
Basic Syntax
-- MySQL
DELIMITER //
CREATE PROCEDURE procedure_name(
[parameter_list]
)
BEGIN
-- SQL statements
END //
DELIMITER ;
-- SQL Server
CREATE PROCEDURE procedure_name
@parameter1 datatype,
@parameter2 datatype
AS
BEGIN
-- SQL statements
END;
-- PostgreSQL
CREATE OR REPLACE PROCEDURE procedure_name(
parameter1 datatype,
parameter2 datatype
)
LANGUAGE plpgsql
AS $$
BEGIN
-- SQL statements
END;
$$;
Example 1: Simple Procedure (MySQL)
-- Create procedure to get customer count
DELIMITER //
CREATE PROCEDURE GetCustomerCount()
BEGIN
SELECT COUNT(*) AS total_customers
FROM customers;
END //
DELIMITER ;
-- Call procedure
CALL GetCustomerCount();
Example 2: Simple Procedure (SQL Server)
-- Create procedure
CREATE PROCEDURE GetCustomerCount
AS
BEGIN
SELECT COUNT(*) AS total_customers
FROM customers;
END;
-- Execute procedure
EXEC GetCustomerCount;
-- or
EXECUTE GetCustomerCount;
Example 3: Procedure with SELECT
-- MySQL: Get all active orders
DELIMITER //
CREATE PROCEDURE GetActiveOrders()
BEGIN
SELECT
o.order_id,
c.first_name,
c.last_name,
o.order_date,
o.total_amount,
o.status
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'Pending'
ORDER BY o.order_date DESC;
END //
DELIMITER ;
-- Call procedure
CALL GetActiveOrders();
Example 4: Procedure with INSERT
-- MySQL: Add new customer
DELIMITER //
CREATE PROCEDURE AddCustomer(
IN p_first_name VARCHAR(50),
IN p_last_name VARCHAR(50),
IN p_email VARCHAR(100),
IN p_phone VARCHAR(20)
)
BEGIN
INSERT INTO customers (first_name, last_name, email, phone)
VALUES (p_first_name, p_last_name, p_email, p_phone);
SELECT LAST_INSERT_ID() AS new_customer_id;
END //
DELIMITER ;
-- Call procedure
CALL AddCustomer('John', 'Doe', '[email protected]', '555-0101');
Example 5: Procedure with UPDATE
-- MySQL: Update order status
DELIMITER //
CREATE PROCEDURE UpdateOrderStatus(
IN p_order_id INT,
IN p_new_status VARCHAR(20)
)
BEGIN
UPDATE orders
SET status = p_new_status
WHERE order_id = p_order_id;
SELECT ROW_COUNT() AS rows_affected;
END //
DELIMITER ;
-- Call procedure
CALL UpdateOrderStatus(1, 'Shipped');
Parameters (IN, OUT, INOUT)
Parameter Types
┌──────────────────────────────────────────────────────────┐
│ PARAMETER TYPES │
├──────────────────────────────────────────────────────────┤
│ │
│ IN Parameters: │
│ - Input only │
│ - Cannot be modified │
│ - Default type │
│ │
│ OUT Parameters: │
│ - Output only │
│ - Returns value to caller │
│ - Initial value is NULL │
│ │
│ INOUT Parameters: │
│ - Both input and output │
│ - Can be modified │
│ - Returns modified value │
│ │
└──────────────────────────────────────────────────────────┘
Parameter Flow Diagram:
┌─────────────────────────────────────────────────────────────┐
│ PARAMETER FLOW IN STORED PROCEDURES │
├─────────────────────────────────────────────────────────────┤
│ │
│ IN Parameter Flow: │
│ ┌──────────┐ ┌──────────────┐ │
│ │ Caller │────────>│ Procedure │ │
│ │ @value │ Pass │ (Read Only) │ │
│ └──────────┘ └──────────────┘ │
│ │
│ OUT Parameter Flow: │
│ ┌──────────┐ ┌──────────────┐ │
│ │ Caller │<────────│ Procedure │ │
│ │ @result │ Return │ (Set Value) │ │
│ └──────────┘ └──────────────┘ │
│ │
│ INOUT Parameter Flow: │
│ ┌──────────┐ ┌──────────────┐ │
│ │ Caller │────────>│ Procedure │ │
│ │ @value │ Pass │ (Modify) │ │
│ │ │<────────│ │ │
│ └──────────┘ Return └──────────────┘ │
│ │
│ Example Flow: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ CALL GetStats( │ │
│ │ IN: customer_id = 123 ──────────┐ │ │
│ │ OUT: total_orders = ? <─────┐ │ │ │
│ │ OUT: total_spent = ? <──┐ │ │ │ │
│ │ INOUT: discount = 10% ───┼──┼───┼──> Proc │ │
│ │ │ │ │ │ │
│ │ Returns: │ │ │ │ │
│ │ total_orders = 5 <───────┘ │ │ │ │
│ │ total_spent = 500.00 <──────────┘ │ │ │
│ │ discount = 15% <──────────────┘ │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
Example 1: IN Parameters
-- MySQL: IN parameters (default)
DELIMITER //
CREATE PROCEDURE GetCustomerOrders(
IN p_customer_id INT
)
BEGIN
SELECT
order_id,
order_date,
total_amount,
status
FROM orders
WHERE customer_id = p_customer_id
ORDER BY order_date DESC;
END //
DELIMITER ;
-- Call with parameter
CALL GetCustomerOrders(1);
Example 2: OUT Parameters
-- MySQL: OUT parameter
DELIMITER //
CREATE PROCEDURE GetOrderTotal(
IN p_order_id INT,
OUT p_total DECIMAL(10,2)
)
BEGIN
SELECT SUM(quantity * unit_price)
INTO p_total
FROM order_items
WHERE order_id = p_order_id;
END //
DELIMITER ;
-- Call and get output
CALL GetOrderTotal(1, @total);
SELECT @total AS order_total;
Example 3: INOUT Parameters
-- MySQL: INOUT parameter
DELIMITER //
CREATE PROCEDURE ApplyDiscount(
INOUT p_amount DECIMAL(10,2),
IN p_discount_percent DECIMAL(5,2)
)
BEGIN
SET p_amount = p_amount * (1 - p_discount_percent / 100);
END //
DELIMITER ;
-- Call with INOUT
SET @price = 100.00;
CALL ApplyDiscount(@price, 10);
SELECT @price AS discounted_price; -- Returns 90.00
Example 4: Multiple OUT Parameters
-- MySQL: Multiple outputs
DELIMITER //
CREATE PROCEDURE GetCustomerStats(
IN p_customer_id INT,
OUT p_total_orders INT,
OUT p_total_spent DECIMAL(10,2),
OUT p_avg_order_value DECIMAL(10,2)
)
BEGIN
SELECT
COUNT(*),
COALESCE(SUM(total_amount), 0),
COALESCE(AVG(total_amount), 0)
INTO p_total_orders, p_total_spent, p_avg_order_value
FROM orders
WHERE customer_id = p_customer_id;
END //
DELIMITER ;
-- Call and get multiple outputs
CALL GetCustomerStats(1, @orders, @spent, @avg);
SELECT @orders, @spent, @avg;
Example 5: SQL Server Parameters
-- SQL Server: OUTPUT parameters
CREATE PROCEDURE GetCustomerStats
@customer_id INT,
@total_orders INT OUTPUT,
@total_spent DECIMAL(10,2) OUTPUT
AS
BEGIN
SELECT
@total_orders = COUNT(*),
@total_spent = COALESCE(SUM(total_amount), 0)
FROM orders
WHERE customer_id = @customer_id;
END;
-- Execute with OUTPUT
DECLARE @orders INT, @spent DECIMAL(10,2);
EXEC GetCustomerStats
@customer_id = 1,
@total_orders = @orders OUTPUT,
@total_spent = @spent OUTPUT;
SELECT @orders AS TotalOrders, @spent AS TotalSpent;
Variables and Data Types
Example 1: Declare Variables (MySQL)
DELIMITER //
CREATE PROCEDURE CalculateOrderTotal(
IN p_order_id INT
)
BEGIN
-- Declare variables
DECLARE v_subtotal DECIMAL(10,2);
DECLARE v_tax DECIMAL(10,2);
DECLARE v_total DECIMAL(10,2);
DECLARE v_tax_rate DECIMAL(5,4) DEFAULT 0.0825;
-- Calculate subtotal
SELECT SUM(quantity * unit_price)
INTO v_subtotal
FROM order_items
WHERE order_id = p_order_id;
-- Calculate tax and total
SET v_tax = v_subtotal * v_tax_rate;
SET v_total = v_subtotal + v_tax;
-- Return results
SELECT
v_subtotal AS subtotal,
v_tax AS tax,
v_total AS total;
END //
DELIMITER ;
Example 2: Variable Assignment
-- MySQL: Different ways to assign variables
DELIMITER //
CREATE PROCEDURE VariableExamples()
BEGIN
DECLARE v_count INT;
DECLARE v_name VARCHAR(50);
DECLARE v_date DATE;
-- Using SET
SET v_count = 10;
SET v_name = 'John Doe';
SET v_date = CURDATE();
-- Using SELECT INTO
SELECT COUNT(*) INTO v_count FROM customers;
-- Multiple assignments
SELECT first_name, last_name
INTO v_name, @last_name
FROM customers
WHERE customer_id = 1;
SELECT v_count, v_name, v_date;
END //
DELIMITER ;
Example 3: SQL Server Variables
-- SQL Server: Variables
CREATE PROCEDURE CalculateDiscount
@order_id INT
AS
BEGIN
DECLARE @subtotal DECIMAL(10,2);
DECLARE @discount DECIMAL(10,2);
DECLARE @final_amount DECIMAL(10,2);
DECLARE @discount_rate DECIMAL(5,2) = 0.10;
-- Get subtotal
SELECT @subtotal = SUM(quantity * unit_price)
FROM order_items
WHERE order_id = @order_id;
-- Calculate discount
SET @discount = @subtotal * @discount_rate;
SET @final_amount = @subtotal - @discount;
-- Return results
SELECT
@subtotal AS Subtotal,
@discount AS Discount,
@final_amount AS FinalAmount;
END;
Control Flow Statements
Control Flow Diagram:
┌─────────────────────────────────────────────────────────────┐
│ CONTROL FLOW STATEMENTS IN PROCEDURES │
├─────────────────────────────────────────────────────────────┤
│ │
│ IF-THEN-ELSE Flow: │
│ ┌──────────┐ │
│ │ Start │ │
│ └────┬─────┘ │
│ │ │
│ ▼ │
│ ┌─────────────┐ │
│ │ Condition? │ │
│ └──┬──────┬───┘ │
│ │ YES │ NO │
│ ▼ ▼ │
│ ┌────┐ ┌────┐ │
│ │ A │ │ B │ │
│ └──┬─┘ └──┬─┘ │
│ └──────┘ │
│ │ │
│ ▼ │
│ ┌──────┐ │
│ │ End │ │
│ └──────┘ │
│ │
│ CASE Statement Flow: │
│ ┌──────────┐ │
│ │ Value │ │
│ └────┬─────┘ │
│ │ │
│ ┌────┴────┬────────┬────────┐ │
│ │ │ │ │ │
│ ▼ ▼ ▼ ▼ │
│ WHEN 1 WHEN 2 WHEN 3 ELSE │
│ │ │ │ │ │
│ ▼ ▼ ▼ ▼ │
│ Action1 Action2 Action3 Default │
│ │ │ │ │ │
│ └─────────┴────────┴────────┘ │
│ │ │
│ ▼ │
│ ┌──────┐ │
│ │ End │ │
│ └──────┘ │
│ │
│ WHILE Loop Flow: │
│ ┌──────────┐ │
│ │ Start │ │
│ └────┬─────┘ │
│ │ │
│ ▼ │
│ ┌─────────────┐ │
│ │ Condition? │◄─────┐ │
│ └──┬──────────┘ │ │
│ │ TRUE │ │
│ ▼ │ │
│ ┌────────────┐ │ │
│ │ Action │───────┘ │
│ └────────────┘ │
│ │ FALSE │
│ ▼ │
│ ┌──────┐ │
│ │ End │ │
│ └──────┘ │
│ │
│ LOOP with LEAVE: │
│ ┌──────────┐ │
│ │ Start │ │
│ └────┬─────┘ │
│ │ │
│ ▼ │
│ ┌────────────┐ │
│ │ LOOP Start │◄─────┐ │
│ └────┬───────┘ │ │
│ │ │ │
│ ▼ │ │
│ ┌─────────────┐ │ │
│ │ Action │ │ │
│ └────┬────────┘ │ │
│ │ │ │
│ ▼ │ │
│ ┌─────────────┐ │ │
│ │ Exit Cond? │─NO──┘ │
│ └────┬────────┘ │
│ │ YES (LEAVE) │
│ ▼ │
│ ┌──────┐ │
│ │ End │ │
│ └──────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
IF-THEN-ELSE
-- MySQL: IF statement
DELIMITER //
CREATE PROCEDURE CheckOrderStatus(
IN p_order_id INT
)
BEGIN
DECLARE v_total DECIMAL(10,2);
DECLARE v_status VARCHAR(50);
SELECT total_amount INTO v_total
FROM orders
WHERE order_id = p_order_id;
IF v_total > 1000 THEN
SET v_status = 'High Value Order';
ELSEIF v_total > 500 THEN
SET v_status = 'Medium Value Order';
ELSE
SET v_status = 'Standard Order';
END IF;
SELECT v_status AS order_classification;
END //
DELIMITER ;
CASE Statement
-- MySQL: CASE statement
DELIMITER //
CREATE PROCEDURE CategorizeCustomer(
IN p_customer_id INT
)
BEGIN
DECLARE v_total_spent DECIMAL(10,2);
DECLARE v_category VARCHAR(20);
SELECT COALESCE(SUM(total_amount), 0)
INTO v_total_spent
FROM orders
WHERE customer_id = p_customer_id;
SET v_category = CASE
WHEN v_total_spent >= 10000 THEN 'VIP'
WHEN v_total_spent >= 5000 THEN 'Gold'
WHEN v_total_spent >= 1000 THEN 'Silver'
ELSE 'Bronze'
END;
SELECT
p_customer_id AS customer_id,
v_total_spent AS total_spent,
v_category AS category;
END //
DELIMITER ;
WHILE Loop
-- MySQL: WHILE loop
DELIMITER //
CREATE PROCEDURE GenerateNumbers(
IN p_max INT
)
BEGIN
DECLARE v_counter INT DEFAULT 1;
DROP TEMPORARY TABLE IF EXISTS temp_numbers;
CREATE TEMPORARY TABLE temp_numbers (
num INT
);
WHILE v_counter <= p_max DO
INSERT INTO temp_numbers VALUES (v_counter);
SET v_counter = v_counter + 1;
END WHILE;
SELECT * FROM temp_numbers;
END //
DELIMITER ;
-- Call procedure
CALL GenerateNumbers(10);
REPEAT Loop
-- MySQL: REPEAT loop
DELIMITER //
CREATE PROCEDURE CalculateFactorial(
IN p_number INT,
OUT p_result BIGINT
)
BEGIN
DECLARE v_counter INT DEFAULT 1;
SET p_result = 1;
REPEAT
SET p_result = p_result * v_counter;
SET v_counter = v_counter + 1;
UNTIL v_counter > p_number
END REPEAT;
END //
DELIMITER ;
-- Call procedure
CALL CalculateFactorial(5, @result);
SELECT @result; -- Returns 120
LOOP Statement
-- MySQL: LOOP with LEAVE
DELIMITER //
CREATE PROCEDURE FindFirstHighValueOrder(
IN p_customer_id INT,
OUT p_order_id INT
)
BEGIN
DECLARE v_done INT DEFAULT FALSE;
DECLARE v_current_order INT;
DECLARE v_amount DECIMAL(10,2);
DECLARE order_cursor CURSOR FOR
SELECT order_id, total_amount
FROM orders
WHERE customer_id = p_customer_id
ORDER BY order_date;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
SET p_order_id = NULL;
OPEN order_cursor;
read_loop: LOOP
FETCH order_cursor INTO v_current_order, v_amount;
IF v_done THEN
LEAVE read_loop;
END IF;
IF v_amount > 1000 THEN
SET p_order_id = v_current_order;
LEAVE read_loop;
END IF;
END LOOP;
CLOSE order_cursor;
END //
DELIMITER ;
SQL Server Control Flow
-- SQL Server: IF-ELSE
CREATE PROCEDURE CheckInventory
@product_id INT
AS
BEGIN
DECLARE @stock INT;
SELECT @stock = stock_quantity
FROM products
WHERE product_id = @product_id;
IF @stock < 10
BEGIN
PRINT 'Low stock - Reorder needed';
-- Send alert
END
ELSE IF @stock < 50
BEGIN
PRINT 'Stock OK';
END
ELSE
BEGIN
PRINT 'Stock high';
END
END;
-- SQL Server: WHILE loop
CREATE PROCEDURE ProcessOrders
AS
BEGIN
DECLARE @order_id INT;
WHILE EXISTS (SELECT 1 FROM orders WHERE status = 'Pending')
BEGIN
SELECT TOP 1 @order_id = order_id
FROM orders
WHERE status = 'Pending';
-- Process order
UPDATE orders
SET status = 'Processing'
WHERE order_id = @order_id;
-- Add delay to prevent infinite loop
WAITFOR DELAY '00:00:01';
END
END;
Error Handling
Error Handling Flow Diagram:
┌─────────────────────────────────────────────────────────────┐
│ ERROR HANDLING IN STORED PROCEDURES │
├─────────────────────────────────────────────────────────────┤
│ │
│ MySQL DECLARE HANDLER Flow: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ BEGIN │ │
│ │ ┌──────────────────────────────────────┐ │ │
│ │ │ DECLARE EXIT HANDLER FOR SQLEXCEPTION│ │ │
│ │ │ - Set error variables │ │ │
│ │ │ - ROLLBACK transaction │ │ │
│ │ └──────────────────────────────────────┘ │ │
│ │ │ │
│ │ START TRANSACTION; │ │
│ │ ┌─────────────────┐ │ │
│ │ │ SQL Operations │ │ │
│ │ └────────┬────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌─────────┐ │ │
│ │ │ Error? │ │ │
│ │ └──┬───┬──┘ │ │
│ │ YES │ │ NO │ │
│ │ ▼ ▼ │ │
│ │ ┌────────┐ ┌────────┐ │ │
│ │ │Handler │ │ COMMIT │ │ │
│ │ │Executes│ └────────┘ │ │
│ │ └────────┘ │ │
│ │ END │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ SQL Server TRY-CATCH Flow: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ BEGIN TRY │ │
│ │ ┌─────────────────────┐ │ │
│ │ │ BEGIN TRANSACTION │ │ │
│ │ └──────────┬──────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌─────────────────────┐ │ │
│ │ │ SQL Operations │ │ │
│ │ └──────────┬──────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌─────────────────────┐ │ │
│ │ │ COMMIT TRANSACTION │ │ │
│ │ └─────────────────────┘ │ │
│ │ END TRY │ │
│ │ │ │ │
│ │ │ Error occurs │ │
│ │ ▼ │ │
│ │ BEGIN CATCH │ │
│ │ ┌─────────────────────┐ │ │
│ │ │ IF @@TRANCOUNT > 0 │ │ │
│ │ │ ROLLBACK │ │ │
│ │ └──────────┬──────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌─────────────────────┐ │ │
│ │ │ Return Error Info │ │ │
│ │ │ - ERROR_NUMBER() │ │ │
│ │ │ - ERROR_MESSAGE() │ │ │
│ │ │ - ERROR_SEVERITY() │ │ │
│ │ └─────────────────────┘ │ │
│ │ END CATCH │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ PostgreSQL EXCEPTION Flow: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ BEGIN │ │
│ │ ┌─────────────────┐ │ │
│ │ │ SQL Operations │ │ │
│ │ └────────┬────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌─────────┐ │ │
│ │ │ Error? │ │ │
│ │ └──┬───┬──┘ │ │
│ │ YES │ │ NO │ │
│ │ ▼ ▼ │ │
│ │ EXCEPTION Success │ │
│ │ ┌──────────────────────┐ │ │
│ │ │ WHEN unique_violation│ │ │
│ │ │ Handle specific │ │ │
│ │ ├──────────────────────┤ │ │
│ │ │ WHEN OTHERS │ │ │
│ │ │ Handle generic │ │ │
│ │ └──────────────────────┘ │ │
│ │ END │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
MySQL Error Handling
-- MySQL: DECLARE HANDLER
DELIMITER //
CREATE PROCEDURE SafeInsertCustomer(
IN p_first_name VARCHAR(50),
IN p_last_name VARCHAR(50),
IN p_email VARCHAR(100),
OUT p_success BOOLEAN,
OUT p_message VARCHAR(255)
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET p_success = FALSE;
SET p_message = 'Error: Could not insert customer';
ROLLBACK;
END;
DECLARE EXIT HANDLER FOR 1062 -- Duplicate entry
BEGIN
SET p_success = FALSE;
SET p_message = 'Error: Email already exists';
END;
START TRANSACTION;
INSERT INTO customers (first_name, last_name, email)
VALUES (p_first_name, p_last_name, p_email);
SET p_success = TRUE;
SET p_message = 'Customer added successfully';
COMMIT;
END //
DELIMITER ;
-- Call procedure
CALL SafeInsertCustomer('John', 'Doe', '[email protected]', @success, @msg);
SELECT @success, @msg;
SQL Server Error Handling
-- SQL Server: TRY-CATCH
CREATE PROCEDURE SafeUpdateOrder
@order_id INT,
@new_status VARCHAR(20)
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
-- Check if order exists
IF NOT EXISTS (SELECT 1 FROM orders WHERE order_id = @order_id)
BEGIN
RAISERROR('Order not found', 16, 1);
END
-- Update order
UPDATE orders
SET status = @new_status
WHERE order_id = @order_id;
-- Log the change
INSERT INTO audit_log (action, table_name, record_id)
VALUES ('UPDATE', 'orders', @order_id);
COMMIT TRANSACTION;
SELECT 'Success' AS Result;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState;
END CATCH
END;
PostgreSQL Error Handling
-- PostgreSQL: EXCEPTION handling
CREATE OR REPLACE PROCEDURE safe_insert_customer(
p_first_name VARCHAR(50),
p_last_name VARCHAR(50),
p_email VARCHAR(100)
)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO customers (first_name, last_name, email)
VALUES (p_first_name, p_last_name, p_email);
RAISE NOTICE 'Customer added successfully';
EXCEPTION
WHEN unique_violation THEN
RAISE NOTICE 'Error: Email already exists';
WHEN OTHERS THEN
RAISE NOTICE 'Error: %', SQLERRM;
END;
$$;
Cursors
Cursors allow row-by-row processing of query results.
Cursor Lifecycle Diagram:
┌─────────────────────────────────────────────────────────────┐
│ CURSOR LIFECYCLE │
├─────────────────────────────────────────────────────────────┤
│ │
│ Complete Cursor Flow: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ 1. DECLARE CURSOR │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ DECLARE cursor_name CURSOR FOR │ │ │
│ │ │ SELECT ... FROM ... WHERE ... │ │ │
│ │ └──────────────┬──────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ 2. DECLARE HANDLER │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ DECLARE CONTINUE HANDLER FOR │ │ │
│ │ │ NOT FOUND SET done = TRUE │ │ │
│ │ └──────────────┬──────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ 3. OPEN CURSOR │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ OPEN cursor_name; │ │ │
│ │ │ - Execute SELECT query │ │ │
│ │ │ - Position before first row │ │ │
│ │ └──────────────┬──────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ 4. FETCH LOOP │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ LOOP │ │ │
│ │ │ FETCH cursor INTO variables │◄─────┐ │ │
│ │ │ │ │ │ │
│ │ │ IF done THEN │ │ │ │
│ │ │ LEAVE loop │ │ │ │
│ │ │ END IF │ │ │ │
│ │ │ │ │ │ │
│ │ │ -- Process row data │ │ │ │
│ │ │ -- Perform operations │ │ │ │
│ │ │ │ │ │ │
│ │ │ END LOOP ───────────────────────┘ │ │ │
│ │ └──────────────┬─────────────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ 5. CLOSE CURSOR │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ CLOSE cursor_name; │ │ │
│ │ │ - Release resources │ │ │
│ │ │ - Free memory │ │ │
│ │ └─────────────────────────────────┘ │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Cursor States: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ [DECLARED] ──OPEN──> [ACTIVE] ──FETCH──> [READING] │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ │ │ ┌───────────┘ │ │
│ │ │ │ │ │
│ │ │ ▼ │ │
│ │ │ [END OF DATA] │ │
│ │ │ │ │ │
│ │ └─CLOSE───┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ [CLOSED] │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Memory & Performance Impact: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ ⚠️ Cursor Overhead: │ │
│ │ • Locks rows during processing │ │
│ │ • Consumes memory for result set │ │
│ │ • Slower than set-based operations │ │
│ │ │ │
│ │ ✅ When to Use: │ │
│ │ • Complex row-by-row logic required │ │
│ │ • Cannot be done with set-based SQL │ │
│ │ • Small result sets │ │
│ │ │ │
│ │ ❌ Avoid When: │ │
│ │ • Set-based operations possible │ │
│ │ • Large result sets │ │
│ │ • Performance is critical │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
Example 1: Basic Cursor (MySQL)
DELIMITER //
CREATE PROCEDURE ProcessAllOrders()
BEGIN
DECLARE v_done INT DEFAULT FALSE;
DECLARE v_order_id INT;
DECLARE v_customer_id INT;
DECLARE v_total DECIMAL(10,2);
-- Declare cursor
DECLARE order_cursor CURSOR FOR
SELECT order_id, customer_id, total_amount
FROM orders
WHERE status = 'Pending';
-- Declare handler for end of cursor
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
-- Open cursor
OPEN order_cursor;
-- Loop through results
read_loop: LOOP
FETCH order_cursor INTO v_order_id, v_customer_id, v_total;
IF v_done THEN
LEAVE read_loop;
END IF;
-- Process each order
IF v_total > 1000 THEN
UPDATE orders
SET status = 'Priority'
WHERE order_id = v_order_id;
END IF;
END LOOP;
-- Close cursor
CLOSE order_cursor;
END //
DELIMITER ;
Example 2: Cursor with Multiple Columns
DELIMITER //
CREATE PROCEDURE GenerateCustomerReport()
BEGIN
DECLARE v_done INT DEFAULT FALSE;
DECLARE v_customer_id INT;
DECLARE v_name VARCHAR(100);
DECLARE v_order_count INT;
DECLARE v_total_spent DECIMAL(10,2);
DECLARE customer_cursor CURSOR FOR
SELECT
c.customer_id,
CONCAT(c.first_name, ' ', c.last_name) AS name,
COUNT(o.order_id) AS order_count,
COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
-- Create temp table for report
DROP TEMPORARY TABLE IF EXISTS customer_report;
CREATE TEMPORARY TABLE customer_report (
customer_id INT,
customer_name VARCHAR(100),
order_count INT,
total_spent DECIMAL(10,2),
category VARCHAR(20)
);
OPEN customer_cursor;
read_loop: LOOP
FETCH customer_cursor INTO v_customer_id, v_name, v_order_count, v_total_spent;
IF v_done THEN
LEAVE read_loop;
END IF;
-- Categorize customer
INSERT INTO customer_report
VALUES (
v_customer_id,
v_name,
v_order_count,
v_total_spent,
CASE
WHEN v_total_spent >= 10000 THEN 'VIP'
WHEN v_total_spent >= 5000 THEN 'Gold'
WHEN v_total_spent >= 1000 THEN 'Silver'
ELSE 'Bronze'
END
);
END LOOP;
CLOSE customer_cursor;
SELECT * FROM customer_report ORDER BY total_spent DESC;
END //
DELIMITER ;
Example 3: SQL Server Cursor
CREATE PROCEDURE ProcessOrdersWithCursor
AS
BEGIN
DECLARE @order_id INT;
DECLARE @total DECIMAL(10,2);
DECLARE order_cursor CURSOR FOR
SELECT order_id, total_amount
FROM orders
WHERE status = 'Pending';
OPEN order_cursor;
FETCH NEXT FROM order_cursor INTO @order_id, @total;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Process order
IF @total > 1000
BEGIN
UPDATE orders
SET status = 'Priority'
WHERE order_id = @order_id;
END
FETCH NEXT FROM order_cursor INTO @order_id, @total;
END
CLOSE order_cursor;
DEALLOCATE order_cursor;
END;
Dynamic SQL
Dynamic SQL allows you to build and execute SQL statements at runtime.
Example 1: MySQL Dynamic SQL
DELIMITER //
CREATE PROCEDURE DynamicSearch(
IN p_table_name VARCHAR(50),
IN p_column_name VARCHAR(50),
IN p_search_value VARCHAR(100)
)
BEGIN
SET @sql = CONCAT(
'SELECT * FROM ', p_table_name,
' WHERE ', p_column_name, ' LIKE ''%', p_search_value, '%'''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
-- Call procedure
CALL DynamicSearch('customers', 'last_name', 'Smith');
Example 2: SQL Server Dynamic SQL
CREATE PROCEDURE DynamicQuery
@table_name NVARCHAR(128),
@where_clause NVARCHAR(MAX)
AS
BEGIN
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT * FROM ' + QUOTENAME(@table_name);
IF @where_clause IS NOT NULL
SET @sql = @sql + N' WHERE ' + @where_clause;
EXEC sp_executesql @sql;
END;
-- Execute
EXEC DynamicQuery 'customers', 'city = ''New York''';
Example 3: Parameterized Dynamic SQL
-- SQL Server: Safe parameterized dynamic SQL
CREATE PROCEDURE SafeDynamicQuery
@customer_id INT
AS
BEGIN
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT * FROM orders WHERE customer_id = @cust_id';
EXEC sp_executesql
@sql,
N'@cust_id INT',
@cust_id = @customer_id;
END;
Transaction Control
Transaction Control Flow Diagram:
┌─────────────────────────────────────────────────────────────┐
│ TRANSACTION CONTROL IN PROCEDURES │
├─────────────────────────────────────────────────────────────┤
│ │
│ Basic Transaction Flow: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ START/BEGIN TRANSACTION │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ Initialize transaction │ │ │
│ │ │ - Lock resources │ │ │
│ │ │ - Start logging │ │ │
│ │ └──────────────┬──────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ Execute SQL Operations │ │ │
│ │ │ - INSERT │ │ │
│ │ │ - UPDATE │ │ │
│ │ │ - DELETE │ │ │
│ │ └──────────────┬──────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌─────────┐ │ │
│ │ │ Error? │ │ │
│ │ └──┬───┬──┘ │ │
│ │ YES │ │ NO │ │
│ │ ▼ ▼ │ │
│ │ ┌─────────┐ ┌─────────┐ │ │
│ │ │ROLLBACK │ │ COMMIT │ │ │
│ │ │- Undo │ │- Save │ │ │
│ │ │- Release│ │- Release│ │ │
│ │ └─────────┘ └─────────┘ │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Transaction with Savepoints: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ BEGIN TRANSACTION │ │
│ │ ┌─────────────────┐ │ │
│ │ │ Operation 1 │ │ │
│ │ └────────┬────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌─────────────────┐ │ │
│ │ │ SAVEPOINT sp1 │ ◄─── Checkpoint 1 │ │
│ │ └────────┬────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌─────────────────┐ │ │
│ │ │ Operation 2 │ │ │
│ │ └────────┬────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌─────────────────┐ │ │
│ │ │ SAVEPOINT sp2 │ ◄─── Checkpoint 2 │ │
│ │ └────────┬────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌─────────────────┐ │ │
│ │ │ Operation 3 │ │ │
│ │ └────────┬────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌─────────┐ │ │
│ │ │ Error? │ │ │
│ │ └──┬───┬──┘ │ │
│ │ YES │ │ NO │ │
│ │ ▼ ▼ │ │
│ │ ┌──────────────┐ ┌────────┐ │ │
│ │ │ROLLBACK TO │ │ COMMIT │ │ │
│ │ │SAVEPOINT sp2 │ └────────┘ │ │
│ │ │(Undo Op 3) │ │ │
│ │ └──────────────┘ │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ ACID Properties in Transactions: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ 🅰️ Atomicity │ │
│ │ All operations succeed or all fail │ │
│ │ ┌────────┐ ┌────────┐ ┌────────┐ │ │
│ │ │ Op 1 │→ │ Op 2 │→ │ Op 3 │ │ │
│ │ └────────┘ └────────┘ └────────┘ │ │
│ │ ↓ ↓ ↓ │ │
│ │ All succeed OR all rollback │ │
│ │ │ │
│ │ 🅲 Consistency │ │
│ │ Database remains in valid state │ │
│ │ Valid State → Transaction → Valid State │ │
│ │ │ │
│ │ 🅸 Isolation │ │
│ │ Transactions don't interfere │ │
│ │ Transaction A ⊥ Transaction B │ │
│ │ │ │
│ │ 🅳 Durability │ │
│ │ Committed changes persist │ │
│ │ COMMIT → Permanent Storage │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Transaction States: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ [INACTIVE] ──BEGIN──> [ACTIVE] ──Operations──> │ │
│ │ │ │ │
│ │ ├──COMMIT──> [COMMITTED] │ │
│ │ │ │ │
│ │ └─ROLLBACK─> [ABORTED] │ │
│ │ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
Example 1: Transaction in Procedure
-- MySQL: Transaction handling
DELIMITER //
CREATE PROCEDURE TransferFunds(
IN p_from_account INT,
IN p_to_account INT,
IN p_amount DECIMAL(10,2),
OUT p_success BOOLEAN,
OUT p_message VARCHAR(255)
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET p_success = FALSE;
SET p_message = 'Transaction failed';
END;
START TRANSACTION;
-- Deduct from source
UPDATE accounts
SET balance = balance - p_amount
WHERE account_id = p_from_account;
-- Add to destination
UPDATE accounts
SET balance = balance + p_amount
WHERE account_id = p_to_account;
COMMIT;
SET p_success = TRUE;
SET p_message = 'Transfer successful';
END //
DELIMITER ;
Example 2: Savepoints
-- MySQL: Using savepoints
DELIMITER //
CREATE PROCEDURE ComplexTransaction()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
END;
START TRANSACTION;
-- First operation
INSERT INTO customers (first_name, last_name, email)
VALUES ('John', 'Doe', '[email protected]');
SAVEPOINT sp1;
-- Second operation
INSERT INTO orders (customer_id, order_date, total_amount)
VALUES (LAST_INSERT_ID(), CURDATE(), 100.00);
SAVEPOINT sp2;
-- Third operation (might fail)
INSERT INTO order_items (order_id, product_name, quantity, unit_price)
VALUES (LAST_INSERT_ID(), 'Product A', 2, 50.00);
-- If we need to rollback to sp2
-- ROLLBACK TO SAVEPOINT sp2;
COMMIT;
END //
DELIMITER ;
Best Practices
1. Use Meaningful Names
-- ✅ Good: Descriptive names
CREATE PROCEDURE GetCustomerOrderHistory(
IN p_customer_id INT,
IN p_start_date DATE,
IN p_end_date DATE
)
-- ❌ Bad: Unclear names
CREATE PROCEDURE proc1(
IN p1 INT,
IN p2 DATE,
IN p3 DATE
)
2. Validate Input Parameters
DELIMITER //
CREATE PROCEDURE SafeUpdateOrder(
IN p_order_id INT,
IN p_status VARCHAR(20)
)
BEGIN
-- Validate parameters
IF p_order_id IS NULL OR p_order_id <= 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Invalid order ID';
END IF;
IF p_status NOT IN ('Pending', 'Processing', 'Shipped', 'Delivered') THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Invalid status value';
END IF;
-- Proceed with update
UPDATE orders
SET status = p_status
WHERE order_id = p_order_id;
END //
DELIMITER ;
3. Use Transactions Appropriately
-- ✅ Good: Transaction for related operations
DELIMITER //
CREATE PROCEDURE CreateOrderWithItems(
IN p_customer_id INT,
IN p_total DECIMAL(10,2)
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
END;
START TRANSACTION;
INSERT INTO orders (customer_id, order_date, total_amount)
VALUES (p_customer_id, CURDATE(), p_total);
-- Additional operations...
COMMIT;
END //
DELIMITER ;
4. Handle Errors Properly
-- ✅ Good: Comprehensive error handling
DELIMITER //
CREATE PROCEDURE SafeOperation()
BEGIN
DECLARE v_error_msg VARCHAR(255);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
v_error_msg = MESSAGE_TEXT;
-- Log error
INSERT INTO error_log (error_message, occurred_at)
VALUES (v_error_msg, NOW());
ROLLBACK;
END;
START TRANSACTION;
-- Operations...
COMMIT;
END //
DELIMITER ;
5. Document Your Procedures
-- ✅ Good: Well-documented procedure
/*
* Procedure: CalculateCustomerLifetimeValue
* Purpose: Calculate total value of customer purchases
* Parameters:
* - p_customer_id: Customer identifier
* - p_include_pending: Include pending orders (default TRUE)
* Returns: Lifetime value as DECIMAL(10,2)
* Author: John Doe
* Created: 2023-01-15
* Modified: 2023-06-20
*/
DELIMITER //
CREATE PROCEDURE CalculateCustomerLifetimeValue(
IN p_customer_id INT,
IN p_include_pending BOOLEAN,
OUT p_lifetime_value DECIMAL(10,2)
)
BEGIN
-- Implementation...
END //
DELIMITER ;
6. Avoid Cursors When Possible
-- ❌ Bad: Using cursor unnecessarily
DELIMITER //
CREATE PROCEDURE UpdateAllPrices()
BEGIN
DECLARE v_done INT DEFAULT FALSE;
DECLARE v_product_id INT;
DECLARE product_cursor CURSOR FOR
SELECT product_id FROM products;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
OPEN product_cursor;
read_loop: LOOP
FETCH product_cursor INTO v_product_id;
IF v_done THEN LEAVE read_loop; END IF;
UPDATE products
SET price = price * 1.1
WHERE product_id = v_product_id;
END LOOP;
CLOSE product_cursor;
END //
DELIMITER ;
-- ✅ Good: Set-based operation
DELIMITER //
CREATE PROCEDURE UpdateAllPrices()
BEGIN
UPDATE products
SET price = price * 1.1;
END //
DELIMITER ;
7. Use Appropriate Parameter Types
-- ✅ Good: Clear parameter types
DELIMITER //
CREATE PROCEDURE ProcessOrder(
IN p_order_id INT, -- Input only
OUT p_total DECIMAL(10,2), -- Output only
INOUT p_discount DECIMAL(5,2) -- Both input and output
)
BEGIN
-- Implementation...
END //
DELIMITER ;
8. Keep Procedures Focused
-- ✅ Good: Single responsibility
CREATE PROCEDURE GetCustomerOrders(IN p_customer_id INT)
CREATE PROCEDURE CalculateOrderTotal(IN p_order_id INT)
CREATE PROCEDURE UpdateOrderStatus(IN p_order_id INT, IN p_status VARCHAR(20))
-- ❌ Bad: Does too much
CREATE PROCEDURE DoEverything(...)
Summary
This section covered:
- ✅ Creating and executing stored procedures
- ✅ IN, OUT, and INOUT parameters
- ✅ Variables and data types
- ✅ Control flow (IF, CASE, WHILE, LOOP)
- ✅ Error handling and transactions
- ✅ Cursors for row-by-row processing
- ✅ Dynamic SQL execution
- ✅ Best practices for procedure development