SQL Stored Procedures

A stored procedure is a prepared SQL code that you can save and reuse. It's a set of SQL statements stored in the database that can be executed with a single call.

Stored Procedure Architecture

graph TB
    A[Client Application] -->|CALL procedure| B[Database Server]
    B --> C[Procedure Cache]
    C -->|Cached?| D{Check Cache}
    D -->|Yes| E[Execute Cached Plan]
    D -->|No| F[Parse & Compile]
    F --> G[Optimize]
    G --> H[Store in Cache]
    H --> E
    E --> I[Execute SQL Statements]
    I --> J[Return Results]
    J --> A
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#FF9800
    style E fill:#9C27B0

Key Points:

• Precompiled: Parsed and optimized once, executed many times
• Performance: Faster execution due to cached execution plans
• Security: Encapsulates logic, controls data access
• Reusability: Write once, call from multiple applications
• Network Traffic: Reduces data transfer between client and server

Parameter Types

graph LR
    A[Stored Procedure Parameters] --> B[IN]
    A --> C[OUT]
    A --> D[INOUT]
    
    B --> B1[Input only]
    B --> B2[Pass values to procedure]
    B --> B3[Default type]
    
    C --> C1[Output only]
    C --> C2[Return values from procedure]
    C --> C3[Must be variable]
    
    D --> D1[Input and Output]
    D --> D2[Pass and return values]
    D --> D3[MySQL specific]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#FF9800
    style D fill:#9C27B0

Key Points:

• IN: Default parameter type, passes values into procedure
• OUT: Returns values from procedure to caller
• INOUT: Both input and output (MySQL)
• SQL Server: Uses OUTPUT keyword for OUT parameters
• Multiple: Can have multiple parameters of different types

Control Flow Execution

flowchart TB
    A[Start Procedure] --> B{IF Condition}
    B -->|True| C[Execute Block 1]
    B -->|False| D{CASE Expression}
    C --> E[Continue]
    D -->|Value 1| F[Execute Block 2]
    D -->|Value 2| G[Execute Block 3]
    D -->|Default| H[Execute Default]
    F --> E
    G --> E
    H --> E
    E --> I{WHILE Loop}
    I -->|Condition True| J[Loop Body]
    J --> I
    I -->|Condition False| K[End Procedure]
    
    style A fill:#4CAF50
    style K fill:#F44336

Key Points:

• IF-THEN-ELSE: Conditional execution based on boolean conditions
• CASE: Multi-way branching based on expression values
• WHILE: Loop while condition is true
• REPEAT: Execute at least once, then check condition
• LOOP: Infinite loop with manual exit using LEAVE

Error Handling Flow

sequenceDiagram
    participant Proc as Stored Procedure
    participant Handler as Error Handler
    participant Trans as Transaction
    participant Client
    
    Proc->>Trans: BEGIN TRANSACTION
    Proc->>Proc: Execute SQL Statement
    
    alt Error Occurs
        Proc->>Handler: Trigger Handler
        Handler->>Trans: ROLLBACK
        Handler->>Client: Return Error Message
    else Success
        Proc->>Trans: COMMIT
        Proc->>Client: Return Success
    end

Key Points:

• TRY-CATCH: SQL Server error handling mechanism
• DECLARE HANDLER: MySQL error handling with conditions
• EXCEPTION: PostgreSQL error handling block
• ROLLBACK: Undo changes on error
• Error Codes: Capture SQLSTATE and error messages

Cursor Processing

flowchart LR
    A[DECLARE Cursor] --> B[OPEN Cursor]
    B --> C[FETCH Row]
    C --> D{More Rows?}
    D -->|Yes| E[Process Row]
    E --> C
    D -->|No| F[CLOSE Cursor]
    F --> G[DEALLOCATE Cursor]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style E fill:#FF9800
    style F fill:#9C27B0
    style G fill:#F44336

Key Points:

• Row-by-Row: Process result set one row at a time
• Performance: Slower than set-based operations
• Memory: Holds result set in memory
• Use Cases: Complex row-level logic, sequential processing
• Avoid: Use set-based operations when possible

Transaction Control

graph TB
    A[BEGIN TRANSACTION] --> B[SQL Statement 1]
    B --> C[SQL Statement 2]
    C --> D{Check Errors}
    D -->|Error| E[ROLLBACK]
    D -->|Success| F[SQL Statement 3]
    F --> G{Check Errors}
    G -->|Error| E
    G -->|Success| H[COMMIT]
    E --> I[Transaction Aborted]
    H --> J[Transaction Complete]
    
    style A fill:#4CAF50
    style H fill:#2196F3
    style E fill:#F44336

Key Points:

• ACID: Ensures atomicity, consistency, isolation, durability
• BEGIN: Start transaction block
• COMMIT: Save all changes permanently
• ROLLBACK: Undo all changes in transaction
• SAVEPOINT: Create intermediate checkpoint for partial rollback

Dynamic SQL Execution

sequenceDiagram
    participant Proc as Stored Procedure
    participant Builder as SQL Builder
    participant Executor as SQL Executor
    participant DB as Database
    
    Proc->>Builder: Build SQL String
    Builder->>Builder: Concatenate with Parameters
    Builder->>Executor: PREPARE Statement
    Executor->>Executor: Parse & Compile
    Executor->>DB: EXECUTE Statement
    DB->>Executor: Return Results
    Executor->>Proc: Return to Caller
    Executor->>Executor: DEALLOCATE Statement

Key Points:

• Runtime Construction: Build SQL statements dynamically
• PREPARE: Parse and compile dynamic SQL
• EXECUTE: Run prepared statement
• Parameters: Use parameterized queries to prevent SQL injection
• DEALLOCATE: Free resources after execution

Code Examples

Basic Stored Procedure

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

-- Call procedure
CALL GetEmployeeCount();

Parameters (IN, OUT, INOUT)

-- IN parameter
CREATE PROCEDURE GetEmployeeByID(IN emp_id INT)
BEGIN
    SELECT * FROM employees WHERE employee_id = emp_id;
END;

-- OUT parameter
CREATE PROCEDURE GetEmployeeSalary(
    IN emp_id INT,
    OUT emp_salary DECIMAL(10,2)
)
BEGIN
    SELECT salary INTO emp_salary
    FROM employees
    WHERE employee_id = emp_id;
END;

-- Call with OUT
CALL GetEmployeeSalary(101, @salary);
SELECT @salary;

Control Flow

CREATE PROCEDURE CalculateBonus(
    IN emp_id INT,
    OUT bonus 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 = emp_salary * 0.15;
    ELSEIF years_service >= 5 THEN
        SET bonus = emp_salary * 0.10;
    ELSE
        SET bonus = emp_salary * 0.05;
    END IF;
END;

Error Handling

-- MySQL
CREATE PROCEDURE SafeTransfer(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Transaction failed' AS message;
    END;
    
    START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE account_id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;
    COMMIT;
    SELECT 'Transaction successful' AS message;
END;

Cursor Example

CREATE PROCEDURE ProcessEmployees()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_id INT;
    DECLARE emp_name VARCHAR(100);
    
    DECLARE emp_cursor CURSOR FOR
        SELECT employee_id, employee_name FROM employees;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN emp_cursor;
    
    read_loop: LOOP
        FETCH emp_cursor INTO emp_id, emp_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- Process each employee
        INSERT INTO audit_log VALUES (emp_id, emp_name, NOW());
    END LOOP;
    
    CLOSE emp_cursor;
END;

Best Practices

1. Meaningful Names: Use descriptive procedure and parameter names
2. Input Validation: Always validate input parameters
3. Error Handling: Implement comprehensive error handling
4. Transactions: Use transactions for data consistency
5. Avoid Cursors: Prefer set-based operations over cursors
6. Documentation: Comment complex logic and parameters
7. Security: Use parameterized queries to prevent SQL injection
8. Testing: Test with various inputs including edge cases

---