Stored Procedure Generator

Generate production-ready stored procedures, functions, triggers, and custom database logic for complex business rules, performance optimization, and transaction safety across PostgreSQL, MySQL, and SQL Server.

When to Use This Command

Use /stored-proc when you need to:

• Implement complex business logic close to the data
• Enforce data integrity constraints beyond foreign keys
• Optimize performance by reducing network round trips
• Implement atomic multi-step operations with transaction safety
• Create reusable database functions for reporting and analytics
• Build database triggers for audit logging and data synchronization

DON'T use this when:

• Business logic frequently changes (better in application layer)
• Logic requires external API calls or file I/O
• Team lacks database development expertise
• Migrating between database systems (vendor lock-in risk)
• Simple CRUD operations sufficient (ORMs handle this)

Design Decisions

This command implements comprehensive stored procedure generation because:

• Encapsulates business logic at database level for data integrity
• Reduces network latency by executing multiple queries in single call
• Provides transaction safety for complex multi-step operations
• Enables code reuse across multiple applications
• Leverages database-specific optimizations (compiled execution plans)

Alternative considered: Application-layer logic

• More portable across database systems
• Easier to test and debug
• Better for frequently changing logic
• Recommended for API-heavy applications

Alternative considered: Database views

• Read-only, no data modification
• Cannot contain procedural logic
• Better query optimizer hints
• Recommended for read-heavy reporting

Prerequisites

Before running this command:

1. Understanding of target database's procedural language (PL/pgSQL, MySQL, T-SQL)
2. Knowledge of business logic requirements and edge cases
3. Database permissions to create procedures/functions
4. Testing framework for stored procedure validation
5. Documentation of expected inputs/outputs and error handling

Implementation Process

Step 1: Analyze Business Logic Requirements

Define inputs, outputs, error conditions, and transaction boundaries.

Step 2: Choose Procedure Type

Select function (returns value), procedure (performs action), or trigger (automated).

Step 3: Implement Core Logic

Write procedural code with proper error handling and transaction management.

Step 4: Add Validation and Security

Implement input validation, SQL injection prevention, and permission checks.

Step 5: Test and Optimize

Test edge cases, measure performance, and optimize execution plans.

Output Format

The command generates:

• procedures/business_logic.sql - Production-ready stored procedures
• functions/calculations.sql - Reusable database functions
• triggers/audit_triggers.sql - Automated data tracking triggers
• tests/procedure_tests.sql - Unit tests for validation
• docs/procedure_api.md - Documentation with usage examples

Code Examples

Example 1: PostgreSQL Complex Business Logic Function

-- Order processing with inventory management and audit logging
CREATE OR REPLACE FUNCTION process_order(
    p_customer_id INTEGER,
    p_order_items JSONB,
    p_shipping_address JSONB
) RETURNS TABLE (
    order_id INTEGER,
    total_amount DECIMAL(10,2),
    status VARCHAR(50),
    estimated_delivery DATE
) AS $$
DECLARE
    v_order_id INTEGER;
    v_total DECIMAL(10,2) := 0;
    v_item JSONB;
    v_product_id INTEGER;
    v_quantity INTEGER;
    v_price DECIMAL(10,2);
    v_available_stock INTEGER;
BEGIN
    -- Validate customer exists and is active
    IF NOT EXISTS (
        SELECT 1 FROM customers
        WHERE customer_id = p_customer_id AND status = 'active'
    ) THEN
        RAISE EXCEPTION 'Customer % not found or inactive', p_customer_id
            USING HINT = 'Check customer_id and status';
    END IF;

    -- Create order record
    INSERT INTO orders (customer_id, order_date, status, shipping_address)
    VALUES (
        p_customer_id,
        CURRENT_TIMESTAMP,
        'pending',
        p_shipping_address
    )
    RETURNING order_id INTO v_order_id;

    -- Process each order item
    FOR v_item IN SELECT * FROM jsonb_array_elements(p_order_items)
    LOOP
        v_product_id := (v_item->>'product_id')::INTEGER;
        v_quantity := (v_item->>'quantity')::INTEGER;

        -- Validate product exists
        SELECT price INTO v_price
        FROM products
        WHERE product_id = v_product_id AND active = true;

        IF v_price IS NULL THEN
            RAISE EXCEPTION 'Product % not found or inactive', v_product_id;
        END IF;

        -- Check inventory availability
        SELECT stock_quantity INTO v_available_stock
        FROM inventory
        WHERE product_id = v_product_id
        FOR UPDATE;  -- Lock row for update

        IF v_available_stock < v_quantity THEN
            RAISE EXCEPTION 'Insufficient stock for product %. Available: %, Requested: %',
                v_product_id, v_available_stock, v_quantity
                USING HINT = 'Reduce quantity or remove from order';
        END IF;

        -- Create order item
        INSERT INTO order_items (order_id, product_id, quantity, unit_price, subtotal)
        VALUES (
            v_order_id,
            v_product_id,
            v_quantity,
            v_price,
            v_quantity * v_price
        );

        -- Update inventory
        UPDATE inventory
        SET stock_quantity = stock_quantity - v_quantity,
            last_updated = CURRENT_TIMESTAMP
        WHERE product_id = v_product_id;

        -- Add to total
        v_total := v_total + (v_quantity * v_price);
    END LOOP;

    -- Update order total
    UPDATE orders
    SET total_amount = v_total,
        status = 'confirmed'
    WHERE order_id = v_order_id;

    -- Calculate estimated delivery (3-5 business days)
    DECLARE
        v_delivery_date DATE;
    BEGIN
        v_delivery_date := CURRENT_DATE + INTERVAL '3 days';

        -- Skip weekends
        WHILE EXTRACT(DOW FROM v_delivery_date) IN (0, 6) LOOP
            v_delivery_date := v_delivery_date + INTERVAL '1 day';
        END LOOP;
    END;

    -- Log audit trail
    INSERT INTO order_audit_log (order_id, action, user_id, timestamp, details)
    VALUES (
        v_order_id,
        'order_created',
        CURRENT_USER,
        CURRENT_TIMESTAMP,
        jsonb_build_object(
            'customer_id', p_customer_id,
            'total_amount', v_total,
            'item_count', jsonb_array_length(p_order_items)
        )
    );

    -- Return order details
    RETURN QUERY
    SELECT
        v_order_id,
        v_total,
        'confirmed'::VARCHAR(50),
        v_delivery_date;

EXCEPTION
    WHEN OTHERS THEN
        -- Log error
        INSERT INTO error_log (function_name, error_message, error_detail, timestamp)
        VALUES (
            'process_order',
            SQLERRM,
            SQLSTATE,
            CURRENT_TIMESTAMP
        );

        -- Re-raise exception
        RAISE;
END;
$$ LANGUAGE plpgsql;

-- Usage example
SELECT * FROM process_order(
    123,  -- customer_id
    '[
        {"product_id": 456, "quantity": 2},
        {"product_id": 789, "quantity": 1}
    ]'::JSONB,
    '{"street": "123 Main St", "city": "Portland", "state": "OR", "zip": "97201"}'::JSONB
);

Example 2: MySQL Stored Procedure with Cursors and Error Handling

-- User activity report generator with aggregations
DELIMITER $$

CREATE PROCEDURE generate_user_activity_report(
    IN p_start_date DATE,
    IN p_end_date DATE,
    IN p_user_type VARCHAR(50)
)
BEGIN
    DECLARE v_user_id INT;
    DECLARE v_username VARCHAR(255);
    DECLARE v_total_logins INT;
    DECLARE v_total_transactions DECIMAL(10,2);
    DECLARE v_done INT DEFAULT FALSE;

    -- Cursor to iterate over users
    DECLARE user_cursor CURSOR FOR
        SELECT user_id, username
        FROM users
        WHERE user_type = p_user_type
          AND created_at <= p_end_date
        ORDER BY username;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

    -- Create temporary results table
    DROP TEMPORARY TABLE IF EXISTS temp_user_report;
    CREATE TEMPORARY TABLE temp_user_report (
        user_id INT,
        username VARCHAR(255),
        total_logins INT,
        total_transactions DECIMAL(10,2),
        avg_transaction_value DECIMAL(10,2),
        last_activity_date DATETIME,
        activity_status VARCHAR(20)
    );

    -- Start transaction
    START TRANSACTION;

    -- Open cursor
    OPEN user_cursor;

    user_loop: LOOP
        FETCH user_cursor INTO v_user_id, v_username;

        IF v_done THEN
            LEAVE user_loop;
        END IF;

        -- Count login attempts
        SELECT COUNT(*) INTO v_total_logins
        FROM login_history
        WHERE user_id = v_user_id
          AND login_date BETWEEN p_start_date AND p_end_date
          AND status = 'success';

        -- Sum transaction amounts
        SELECT COALESCE(SUM(amount), 0) INTO v_total_transactions
        FROM transactions
        WHERE user_id = v_user_id
          AND transaction_date BETWEEN p_start_date AND p_end_date
          AND status = 'completed';

        -- Insert aggregated data
        INSERT INTO temp_user_report (
            user_id,
            username,
            total_logins,
            total_transactions,
            avg_transaction_value,
            last_activity_date,
            activity_status
        )
        SELECT
            v_user_id,
            v_username,
            v_total_logins,
            v_total_transactions,
            CASE
                WHEN v_total_logins > 0 THEN v_total_transactions / v_total_logins
                ELSE 0
            END,
            (SELECT MAX(activity_date)
             FROM (
                 SELECT MAX(login_date) AS activity_date FROM login_history WHERE user_id = v_user_id
                 UNION ALL
                 SELECT MAX(transaction_date) FROM transactions WHERE user_id = v_user_id
             ) activities),
            CASE
                WHEN v_total_logins >= 20 THEN 'highly_active'
                WHEN v_total_logins >= 5 THEN 'active'
                WHEN v_total_logins > 0 THEN 'low_activity'
                ELSE 'inactive'
            END;

    END LOOP;

    CLOSE user_cursor;

    -- Commit transaction
    COMMIT;

    -- Return final report
    SELECT
        user_id,
        username,
        total_logins,
        FORMAT(total_transactions, 2) AS total_transactions,
        FORMAT(avg_transaction_value, 2) AS avg_transaction_value,
        DATE_FORMAT(last_activity_date, '%Y-%m-%d %H:%i:%s') AS last_activity,
        activity_status
    FROM temp_user_report
    ORDER BY total_transactions DESC;

END$$

DELIMITER ;

-- Usage
CALL generate_user_activity_report('2024-01-01', '2024-12-31', 'premium');

Example 3: Database Triggers for Audit Logging

-- PostgreSQL audit trigger for tracking all table changes
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (
            table_name,
            operation,
            row_id,
            new_data,
            changed_by,
            changed_at
        ) VALUES (
            TG_TABLE_NAME,
            'INSERT',
            NEW.id,
            row_to_json(NEW),
            CURRENT_USER,
            CURRENT_TIMESTAMP
        );
        RETURN NEW;

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (
            table_name,
            operation,
            row_id,
            old_data,
            new_data,
            changed_by,
            changed_at
        ) VALUES (
            TG_TABLE_NAME,
            'UPDATE',
            NEW.id,
            row_to_json(OLD),
            row_to_json(NEW),
            CURRENT_USER,
            CURRENT_TIMESTAMP
        );
        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (
            table_name,
            operation,
            row_id,
            old_data,
            changed_by,
            changed_at
        ) VALUES (
            TG_TABLE_NAME,
            'DELETE',
            OLD.id,
            row_to_json(OLD),
            CURRENT_USER,
            CURRENT_TIMESTAMP
        );
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Apply audit trigger to sensitive tables
CREATE TRIGGER users_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

CREATE TRIGGER orders_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

CREATE TRIGGER transactions_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON transactions
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

Error Handling

Error	Cause	Solution
"Function does not exist"	Missing or misspelled function name	Check function signature and schema
"Deadlock detected"	Concurrent transactions locking same rows	Use FOR UPDATE SKIP LOCKED or retry logic
"Stack depth limit exceeded"	Infinite recursion in function	Add recursion depth limit checks
"Out of shared memory"	Too many cursors or temp tables	Close cursors explicitly, limit temp table size
"Division by zero"	Unhandled edge case	Add NULL/zero checks before calculations

Configuration Options

Function Types

• RETURNS TABLE: Multi-row result sets
• RETURNS SETOF: Dynamic result sets
• RETURNS VOID: No return value (procedures)
• RETURNS TRIGGER: Trigger functions

Volatility Categories

• IMMUTABLE: Pure function, same inputs = same outputs
• STABLE: Results consistent within transaction
• VOLATILE: May change even with same inputs (default)

Security Options

• SECURITY DEFINER: Runs with creator's permissions
• SECURITY INVOKER: Runs with caller's permissions (default)

Best Practices

DO:

• Use explicit parameter names (p_customer_id, not just id)
• Handle all possible error conditions with meaningful messages
• Use transactions for multi-step operations
• Close cursors explicitly to free resources
• Add input validation at function start
• Document parameters and return values

DON'T:

• Use SELECT * in production functions (specify columns)
• Perform network I/O or file operations in functions
• Create overly complex logic (split into multiple functions)
• Ignore SQL injection risks in dynamic SQL
• Forget to handle NULL values
• Use SECURITY DEFINER without careful permission checks

Performance Considerations

• Functions add ~1-5ms overhead per call (acceptable for complex logic)
• Cached execution plans provide 10-50% speedup on repeated calls
• Triggers add overhead to every INSERT/UPDATE/DELETE (use sparingly)
• Use FOR UPDATE SKIP LOCKED to avoid blocking in high-concurrency scenarios
• Avoid cursors when set-based operations possible (cursors 10-100x slower)
• Index columns used in function WHERE clauses

Related Commands

• /database-migration-manager - Deploy stored procedures across environments
• /sql-query-optimizer - Optimize queries within procedures
• /database-transaction-monitor - Monitor procedure execution and locks
• /database-security-scanner - Audit procedure permissions and SQL injection risks

Version History

• v1.0.0 (2024-10): Initial implementation with PostgreSQL, MySQL, SQL Server support
• Planned v1.1.0: Add automated procedure testing framework and performance profiling