stored-proc

name	stored-proc
description	Generate production-ready stored procedures and database functions
shortcut	stor

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)
     (
        p_customer_id,
        ,
        ,
        p_shipping_address
    )
    RETURNING order_id  v_order_id;

    
     v_item     jsonb_array_elements(p_order_items)
    LOOP
        v_product_id : (v_item)::;
        v_quantity : (v_item)::;

        
         price  v_price
         products
         product_id  v_product_id  active  ;

        IF v_price   
            RAISE EXCEPTION , v_product_id;
         IF;

        
         stock_quantity  v_available_stock
         inventory
         product_id  v_product_id
         ;  

        IF v_available_stock  v_quantity 
            RAISE EXCEPTION ,
                v_product_id, v_available_stock, v_quantity
                 HINT  ;
         IF;

        
         order_items (order_id, product_id, quantity, unit_price, subtotal)
         (
            v_order_id,
            v_product_id,
            v_quantity,
            v_price,
            v_quantity  v_price
        );

        
         inventory
         stock_quantity  stock_quantity  v_quantity,
            last_updated  
         product_id  v_product_id;

        
        v_total : v_total  (v_quantity  v_price);
     LOOP;

    
     orders
     total_amount  v_total,
        status  
     order_id  v_order_id;

    
    
        v_delivery_date ;
    
        v_delivery_date :    ;

        
        WHILE (DOW  v_delivery_date)  (, ) LOOP
            v_delivery_date : v_delivery_date   ;
         LOOP;
    ;

    
     order_audit_log (order_id, action, user_id, , details)
     (
        v_order_id,
        ,
        ,
        ,
        jsonb_build_object(
            , p_customer_id,
            , v_total,
            , jsonb_array_length(p_order_items)
        )
    );

    
     QUERY
    
        v_order_id,
        v_total,
        ::(),
        v_delivery_date;

EXCEPTION
     OTHERS 
        
         error_log (function_name, error_message, error_detail, )
         (
            ,
            SQLERRM,
            ,
            
        );

        
        RAISE;
;
$$  plpgsql;


   process_order(
    ,  
    ::JSONB,
    ::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 ,
        username (),
        total_logins ,
        total_transactions (,),
        avg_transaction_value (,),
        last_activity_date DATETIME,
        activity_status ()
    );

    
     TRANSACTION;

    
     user_cursor;

    user_loop: LOOP
         user_cursor  v_user_id, v_username;

        IF v_done 
            LEAVE user_loop;
         IF;

        
         ()  v_total_logins
         login_history
         user_id  v_user_id
           login_date  p_start_date  p_end_date
           status  ;

        
         ((amount), )  v_total_transactions
         transactions
         user_id  v_user_id
           transaction_date  p_start_date  p_end_date
           status  ;

        
         temp_user_report (
            user_id,
            username,
            total_logins,
            total_transactions,
            avg_transaction_value,
            last_activity_date,
            activity_status
        )
        
            v_user_id,
            v_username,
            v_total_logins,
            v_total_transactions,
            
                 v_total_logins    v_total_transactions  v_total_logins
                 
            ,
            ( (activity_date)
              (
                  (login_date)  activity_date  login_history  user_id  v_user_id
                  
                  (transaction_date)  transactions  user_id  v_user_id
             ) activities),
            
                 v_total_logins    
                 v_total_logins    
                 v_total_logins    
                 
            ;

     LOOP;

     user_cursor;

    
    ;

    
    
        user_id,
        username,
        total_logins,
        FORMAT(total_transactions, )  total_transactions,
        FORMAT(avg_transaction_value, )  avg_transaction_value,
        DATE_FORMAT(last_activity_date, )  last_activity,
        activity_status
     temp_user_report
      total_transactions ;

$$

DELIMITER ;


 generate_user_activity_report(, , );

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),
            ,
            
        );
         ;
     IF;
;
$$  plpgsql;


  users_audit_trigger
    AFTER       users
         audit_trigger_function();

  orders_audit_trigger
    AFTER       orders
         audit_trigger_function();

  transactions_audit_trigger
    AFTER       transactions
         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