Stored Procedures and Functions

Overview

Stored procedures and functions are database objects that execute SQL statements and procedural logic on the database server. A stored procedure performs operations that may modify data and return multiple result sets, while a function computes and returns a single value or table. Both accept input parameters, execute precompiled code, and run with the privileges of the database user who created them.

Database systems compile and optimize stored procedures and functions when created, storing the execution plan for reuse. When an application calls a stored procedure, the database server executes the precompiled code without parsing SQL statements each time. This compilation happens once, reducing overhead on subsequent executions.

The distinction between procedures and functions centers on their return behavior and usage context. Functions return a value and can appear in SQL expressions, while procedures execute as standalone statements and may return multiple result sets or output parameters. A function calculating a discount percentage can embed directly in a SELECT statement, but a procedure processing an order executes as a separate call.

-- Function returning a scalar value
CREATE FUNCTION calculate_discount(amount DECIMAL, tier VARCHAR)
RETURNS DECIMAL
BEGIN
  RETURN CASE tier
    WHEN 'gold' THEN amount * 0.20
    WHEN 'silver' THEN amount * 0.10
    ELSE amount * 0.05
  END;
END;

-- Stored procedure with multiple operations
CREATE PROCEDURE process_order(
  IN customer_id INT,
  IN order_total DECIMAL,
  OUT order_number INT
)
BEGIN
  INSERT INTO orders (customer_id, total, created_at)
  VALUES (customer_id, order_total, NOW());
  
  SET order_number = LAST_INSERT_ID();
  
  UPDATE customers
  SET total_spent = total_spent + order_total
  WHERE id = customer_id;
END;

Most relational databases support stored procedures and functions, including PostgreSQL, MySQL, Oracle, SQL Server, and DB2. Each database system implements its own procedural language: PL/pgSQL for PostgreSQL, Transact-SQL for SQL Server, PL/SQL for Oracle. The syntax and capabilities vary significantly between systems, though basic concepts remain consistent.

Key Principles

Stored procedures and functions execute within the database server process, accessing data without network round trips between application and database. When an application needs to perform multiple related operations, sending individual SQL statements requires multiple network calls. A stored procedure combines these operations into a single server-side execution, reducing latency and network traffic.

Parameter handling differs between procedures and functions. Stored procedures support IN parameters for input values, OUT parameters for return values, and INOUT parameters that serve both purposes. Functions accept only input parameters and communicate results through their return value. The parameter modes determine how data flows between the caller and the database routine.

-- Procedure with multiple parameter modes
CREATE PROCEDURE get_customer_stats(
  IN customer_id INT,
  OUT order_count INT,
  OUT total_spent DECIMAL,
  INOUT status_flag VARCHAR
)
BEGIN
  SELECT COUNT(*), SUM(total)
  INTO order_count, total_spent
  FROM orders
  WHERE customer_id = customer_id;
  
  IF total_spent > 10000 THEN
    SET status_flag = 'premium';
  END IF;
END;

Transaction control within stored procedures enables atomic operations across multiple statements. A procedure can start a transaction, perform several data modifications, and commit or roll back based on conditions. If any operation fails, the procedure rolls back all changes, maintaining data consistency. This transaction boundary cannot extend beyond the procedure's execution scope in most databases.

The execution context determines which database objects a procedure can access. Procedures execute with either definer rights or invoker rights. Definer rights execute with the permissions of the user who created the procedure, allowing controlled access to restricted data. Invoker rights execute with the caller's permissions, applying the same access controls as direct SQL execution.

Stored functions must remain deterministic or non-deterministic based on their declaration. A deterministic function returns the same result for the same inputs, enabling query optimization and index usage. Non-deterministic functions may return different results for identical inputs, such as functions using random numbers or current timestamps. The database system relies on this declaration for optimization decisions.

-- Deterministic function for query optimization
CREATE FUNCTION calculate_tax(amount DECIMAL, rate DECIMAL)
RETURNS DECIMAL
DETERMINISTIC
BEGIN
  RETURN amount * rate;
END;

-- Non-deterministic function with varying results
CREATE FUNCTION generate_order_code()
RETURNS VARCHAR
NOT DETERMINISTIC
BEGIN
  RETURN CONCAT('ORD-', UNIX_TIMESTAMP(), '-', FLOOR(RAND() * 1000));
END;

Result set handling separates procedures from functions. Stored procedures can return zero or more result sets by executing SELECT statements without INTO clauses. The calling application receives each result set sequentially. Functions return a single scalar value or table, integrating directly into SQL queries. A function cannot execute SELECT statements that generate result sets for the caller.

Error handling within procedural code uses condition handlers and exception blocks. When an error occurs during execution, the procedure can catch the error, log information, perform cleanup operations, or re-raise the error to the caller. Unhandled errors automatically roll back any uncommitted transactions, though this behavior varies between database systems.

Temporary tables and variables provide working storage during procedure execution. A procedure can create temporary tables visible only within the current session, populate them with intermediate results, and query them as needed. Local variables hold scalar values for calculations and control flow. These objects exist only for the procedure's duration and do not persist after execution completes.

Design Considerations

The decision to implement logic in stored procedures versus application code involves trade-offs in maintainability, performance, and deployment complexity. Stored procedures centralize business rules within the database, ensuring all applications enforce the same logic regardless of programming language or framework. Application code distributes logic across multiple codebases, complicating consistency but improving portability.

Database vendor lock-in intensifies with stored procedure usage. Each database system implements its own procedural language with incompatible syntax and features. A codebase with extensive stored procedures cannot easily migrate to a different database platform without rewriting all procedural code. Application logic written in Ruby, Python, or Java runs on any database with standard SQL support.

Testing and debugging stored procedures requires database-specific tools and techniques. Unit testing a stored procedure involves creating test data, executing the procedure within a transaction, asserting results, and rolling back changes. Modern application code benefits from established testing frameworks, mocking libraries, and IDE integration. Debugging a stored procedure often means examining execution with database-specific debuggers or inserting logging statements.

Version control and deployment processes differ between stored procedures and application code. Stored procedures exist as database objects, requiring schema migration tools to manage versions. An application deployment must include database migrations that create, modify, or drop stored procedures. Source control tracks procedure definitions as SQL scripts, but the actual running code resides in the database until migrations execute.

-- Migration creating a stored procedure
CREATE OR REPLACE PROCEDURE archive_old_orders()
BEGIN
  INSERT INTO orders_archive
  SELECT * FROM orders
  WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);
  
  DELETE FROM orders
  WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);
END;

Performance characteristics favor stored procedures for data-intensive operations with multiple database round trips. A procedure that validates customer data, inserts an order, updates inventory, and creates audit records completes all operations within the database server. The equivalent application code sends multiple SQL statements over the network, incurring latency for each round trip. The performance advantage grows with operation count and network latency.

Data access patterns influence the stored procedure decision. Operations that filter, transform, and aggregate large datasets benefit from server-side execution. The database engine processes data using indexes and optimized algorithms without transferring rows to the application. Application code that fetches large result sets for processing consumes network bandwidth and memory.

Security requirements sometimes necessitate stored procedures. When applications should not have direct table access, stored procedures provide a controlled interface with parameter validation and access checks. The procedure executes with elevated privileges while applications run with minimal permissions. This security model prevents SQL injection attacks and restricts data access to defined operations.

Team expertise affects stored procedure adoption. Teams proficient in database development and comfortable with procedural SQL can maintain stored procedures effectively. Teams focused on application development may struggle with the different paradigms, testing approaches, and debugging tools required for database programming. The learning curve for database-specific procedural languages adds training overhead.

Horizontal scaling challenges emerge with business logic in stored procedures. Scaling application servers is straightforward: add more servers behind a load balancer. Scaling database servers with stored procedures means scaling the entire database tier, including replication, sharding, and connection pooling complexity. Application logic scales independently from data storage.

Ruby Implementation

Ruby applications interact with stored procedures and functions through database adapter gems. The specific API depends on the database system and gem used. ActiveRecord, Sequel, and low-level adapters like pg and mysql2 provide mechanisms to call stored procedures and retrieve results.

PostgreSQL stored functions (procedures are called functions in PostgreSQL) can be invoked using ActiveRecord's connection object. The execute method runs raw SQL, including function calls. For functions returning scalar values, use SELECT statements with the function invocation.

# Calling a PostgreSQL function that returns a scalar value
class OrderCalculator
  def self.calculate_discount(amount, tier)
    result = ActiveRecord::Base.connection.execute(
      "SELECT calculate_discount(#{amount}, '#{tier}')"
    )
    result.first['calculate_discount'].to_f
  end
end

# Using parameterized queries for safety
class CustomerService
  def self.get_customer_stats(customer_id)
    sql = "SELECT * FROM get_customer_stats($1)"
    result = ActiveRecord::Base.connection.exec_query(sql, 'SQL', [[nil, customer_id]])
    result.first
  end
end

The pg gem provides direct access to PostgreSQL stored procedures through the connection object. Use the exec_params method for parameterized queries, which prevents SQL injection and handles type conversion. The result object contains rows, fields, and metadata from the stored procedure execution.

require 'pg'

class DatabaseConnection
  def initialize
    @conn = PG.connect(dbname: 'myapp', user: 'postgres')
  end
  
  def call_stored_procedure(customer_id)
    # Call procedure with output parameters
    sql = <<-SQL
      CALL process_customer_order($1, $2, $3)
    SQL
    
    result = @conn.exec_params(sql, [customer_id, 100.00, 'pending'])
    result.each do |row|
      puts "Order: #{row['order_number']}"
    end
  end
  
  def call_function_returning_table
    # Call function that returns a table
    sql = "SELECT * FROM get_recent_orders($1)"
    result = @conn.exec_params(sql, [30])
    
    result.map do |row|
      {
        id: row['id'].to_i,
        customer_id: row['customer_id'].to_i,
        total: row['total'].to_f,
        created_at: row['created_at']
      }
    end
  end
end

MySQL stored procedures require a different approach due to MySQL's protocol for handling multiple result sets. The mysql2 gem supports the client-side flag CLIENT_MULTI_RESULTS to enable stored procedure calls that return result sets. Without this flag, calling a stored procedure raises an error.

require 'mysql2'

class MysqlProcedureCaller
  def initialize
    @client = Mysql2::Client.new(
      host: 'localhost',
      username: 'root',
      database: 'myapp',
      flags: Mysql2::Client::MULTI_RESULTS
    )
  end
  
  def call_procedure_with_results(customer_id)
    results = []
    
    # Call stored procedure
    @client.query("CALL get_customer_orders(#{customer_id})")
    
    # Process first result set
    result = @client.store_result
    result.each do |row|
      results << row
    end
    
    # MySQL procedures may return multiple result sets
    # Move to next result set if it exists
    while @client.next_result
      result = @client.store_result
      # Process additional result sets if needed
    end
    
    results
  end
  
  def call_function(amount, rate)
    # Call a MySQL function
    result = @client.query("SELECT calculate_tax(#{amount}, #{rate}) as tax")
    result.first['tax'].to_f
  end
end

ActiveRecord doesn't provide a high-level API for stored procedures, requiring raw SQL execution. Creating a wrapper class provides a cleaner interface and handles parameter binding, result processing, and error handling.

class StoredProcedure
  def self.call(procedure_name, *args)
    placeholders = args.each_with_index.map { |_, i| "$#{i + 1}" }.join(', ')
    sql = "SELECT * FROM #{procedure_name}(#{placeholders})"
    
    result = ActiveRecord::Base.connection.exec_query(
      sql,
      'Stored Procedure',
      args.map { |arg| [nil, arg] }
    )
    
    result.to_a
  end
  
  def self.call_procedure(procedure_name, *args)
    placeholders = args.each_with_index.map { |_, i| "$#{i + 1}" }.join(', ')
    sql = "CALL #{procedure_name}(#{placeholders})"
    
    ActiveRecord::Base.connection.execute(sql, args)
  end
end

# Usage
class OrderService
  def process_order(customer_id, items)
    result = StoredProcedure.call('create_order', customer_id, items.to_json)
    result.first
  end
end

Handling stored procedure errors requires catching database-specific exceptions and extracting error information. PostgreSQL raises PG::Error for stored procedure failures, while MySQL raises Mysql2::Error. The error object contains the original SQL state code and error message from the database.

class ProcedureExecutor
  def safe_execute(procedure_name, *args)
    begin
      StoredProcedure.call(procedure_name, *args)
    rescue ActiveRecord::StatementInvalid => e
      # Parse database error
      if e.message.include?('insufficient_funds')
        raise InsufficientFundsError, "Cannot process transaction: #{e.message}"
      elsif e.message.include?('invalid_customer')
        raise InvalidCustomerError, "Customer not found or inactive"
      else
        raise DatabaseError, "Stored procedure error: #{e.message}"
      end
    rescue PG::Error, Mysql2::Error => e
      # Handle low-level database errors
      Rails.logger.error("Database error calling #{procedure_name}: #{e.message}")
      raise DatabaseConnectionError, "Database unavailable"
    end
  end
end

Output parameters from stored procedures require special handling depending on the database system. PostgreSQL functions return values directly, while MySQL procedures use OUT parameters accessed through session variables.

# PostgreSQL function with multiple return values
class PostgresService
  def self.get_statistics(customer_id)
    sql = <<-SQL
      SELECT 
        order_count,
        total_spent,
        status_flag
      FROM get_customer_stats($1)
    SQL
    
    result = ActiveRecord::Base.connection.exec_query(sql, 'SQL', [[nil, customer_id]])
    result.first
  end
end

# MySQL procedure with OUT parameters
class MysqlService
  def self.process_with_output(customer_id)
    client = Mysql2::Client.new(...)
    
    # Set user variables for OUT parameters
    client.query("SET @order_num = 0")
    client.query("SET @status = ''")
    
    # Call procedure with OUT parameters
    client.query("CALL process_order(#{customer_id}, @order_num, @status)")
    
    # Retrieve OUT parameter values
    result = client.query("SELECT @order_num as order_number, @status as status")
    result.first
  end
end

Practical Examples

A customer management system uses stored procedures to handle complex business operations that span multiple tables. The procedure ensures data consistency by executing all operations within a single transaction, rolling back if any step fails.

CREATE PROCEDURE update_customer_status(
  IN p_customer_id INT,
  IN p_new_status VARCHAR,
  OUT p_previous_status VARCHAR,
  OUT p_affected_orders INT
)
BEGIN
  DECLARE v_error BOOLEAN DEFAULT FALSE;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET v_error = TRUE;
  
  START TRANSACTION;
  
  -- Get previous status
  SELECT status INTO p_previous_status
  FROM customers
  WHERE id = p_customer_id
  FOR UPDATE;
  
  -- Update customer status
  UPDATE customers
  SET status = p_new_status,
      updated_at = NOW()
  WHERE id = p_customer_id;
  
  -- Update related orders
  UPDATE orders
  SET customer_status = p_new_status
  WHERE customer_id = p_customer_id
    AND status IN ('pending', 'processing');
  
  SET p_affected_orders = ROW_COUNT();
  
  -- Insert audit record
  INSERT INTO status_changes (customer_id, old_status, new_status, changed_at)
  VALUES (p_customer_id, p_previous_status, p_new_status, NOW());
  
  IF v_error THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Failed to update customer status';
  ELSE
    COMMIT;
  END IF;
END;

The Ruby application calls this procedure and handles the results:

class CustomerStatusUpdater
  def update_status(customer_id, new_status)
    sql = <<-SQL
      CALL update_customer_status($1, $2, $3, $4)
    SQL
    
    begin
      result = ActiveRecord::Base.connection.execute(
        ActiveRecord::Base.sanitize_sql([sql, customer_id, new_status, nil, nil])
      )
      
      # Extract output parameters from result
      row = result.first
      
      {
        success: true,
        previous_status: row['p_previous_status'],
        affected_orders: row['p_affected_orders'].to_i
      }
    rescue ActiveRecord::StatementInvalid => e
      Rails.logger.error("Status update failed: #{e.message}")
      { success: false, error: e.message }
    end
  end
end

A reporting system uses functions to perform complex calculations that would be inefficient in application code. The function processes large datasets using database indexes and aggregations, returning summarized results.

CREATE FUNCTION calculate_monthly_metrics(p_year INT, p_month INT)
RETURNS TABLE (
  metric_name VARCHAR,
  metric_value DECIMAL,
  comparison_previous_month DECIMAL
)
LANGUAGE plpgsql
AS $$
DECLARE
  v_current_start DATE;
  v_current_end DATE;
  v_previous_start DATE;
  v_previous_end DATE;
BEGIN
  v_current_start := make_date(p_year, p_month, 1);
  v_current_end := v_current_start + INTERVAL '1 month' - INTERVAL '1 day';
  v_previous_start := v_current_start - INTERVAL '1 month';
  v_previous_end := v_current_start - INTERVAL '1 day';
  
  -- Revenue metrics
  RETURN QUERY
  SELECT 
    'revenue'::VARCHAR,
    SUM(total)::DECIMAL,
    (SUM(total) / NULLIF(prev_total, 0) - 1) * 100
  FROM orders
  CROSS JOIN (
    SELECT SUM(total) as prev_total
    FROM orders
    WHERE created_at BETWEEN v_previous_start AND v_previous_end
  ) prev
  WHERE created_at BETWEEN v_current_start AND v_current_end;
  
  -- Order count metrics
  RETURN QUERY
  SELECT
    'order_count'::VARCHAR,
    COUNT(*)::DECIMAL,
    (COUNT(*) / NULLIF(prev_count, 0) - 1) * 100
  FROM orders
  CROSS JOIN (
    SELECT COUNT(*) as prev_count
    FROM orders
    WHERE created_at BETWEEN v_previous_start AND v_previous_end
  ) prev
  WHERE created_at BETWEEN v_current_start AND v_current_end;
END;
$$;

The Ruby application retrieves and formats these metrics:

class MonthlyReport
  def generate(year, month)
    sql = "SELECT * FROM calculate_monthly_metrics($1, $2)"
    results = ActiveRecord::Base.connection.exec_query(
      sql,
      'Monthly Metrics',
      [[nil, year], [nil, month]]
    )
    
    metrics = results.map do |row|
      {
        name: row['metric_name'],
        value: row['metric_value'].to_f,
        change_percentage: row['comparison_previous_month'].to_f.round(2)
      }
    end
    
    format_report(metrics)
  end
  
  private
  
  def format_report(metrics)
    metrics.each_with_object({}) do |metric, hash|
      hash[metric[:name]] = {
        current: metric[:value],
        change: "#{metric[:change_percentage] > 0 ? '+' : ''}#{metric[:change_percentage]}%"
      }
    end
  end
end

A data validation procedure checks business rules before allowing data modifications. The procedure returns a result set with validation errors, allowing the application to present detailed feedback.

CREATE PROCEDURE validate_order_data(
  IN p_customer_id INT,
  IN p_items JSON,
  IN p_shipping_address JSON
)
BEGIN
  CREATE TEMPORARY TABLE validation_errors (
    field VARCHAR,
    error_message VARCHAR
  );
  
  -- Validate customer exists and is active
  IF NOT EXISTS (
    SELECT 1 FROM customers 
    WHERE id = p_customer_id AND status = 'active'
  ) THEN
    INSERT INTO validation_errors VALUES ('customer_id', 'Invalid or inactive customer');
  END IF;
  
  -- Validate items exist and have sufficient inventory
  IF JSON_LENGTH(p_items) = 0 THEN
    INSERT INTO validation_errors VALUES ('items', 'Order must contain at least one item');
  END IF;
  
  -- Validate shipping address format
  IF JSON_EXTRACT(p_shipping_address, '$.postal_code') IS NULL THEN
    INSERT INTO validation_errors VALUES ('shipping_address', 'Postal code is required');
  END IF;
  
  -- Check credit limit
  IF (
    SELECT total_spent + JSON_EXTRACT(p_items, '$[*].price')
    FROM customers WHERE id = p_customer_id
  ) > (
    SELECT credit_limit FROM customers WHERE id = p_customer_id
  ) THEN
    INSERT INTO validation_errors VALUES ('credit_limit', 'Order exceeds customer credit limit');
  END IF;
  
  -- Return all validation errors
  SELECT * FROM validation_errors;
  
  DROP TEMPORARY TABLE validation_errors;
END;

The Ruby validation service calls this procedure and returns structured errors:

class OrderValidator
  ValidationError = Struct.new(:field, :message)
  
  def validate(customer_id, items, shipping_address)
    sql = "CALL validate_order_data($1, $2, $3)"
    
    result = ActiveRecord::Base.connection.exec_query(
      sql,
      'Order Validation',
      [
        [nil, customer_id],
        [nil, items.to_json],
        [nil, shipping_address.to_json]
      ]
    )
    
    errors = result.map do |row|
      ValidationError.new(row['field'], row['error_message'])
    end
    
    ValidationResult.new(errors)
  end
end

class ValidationResult
  attr_reader :errors
  
  def initialize(errors)
    @errors = errors
  end
  
  def valid?
    errors.empty?
  end
  
  def errors_by_field
    errors.group_by(&:field).transform_values do |field_errors|
      field_errors.map(&:message)
    end
  end
end

Performance Considerations

Network latency dominates performance in chatty applications that execute multiple SQL statements sequentially. Each round trip between application and database introduces milliseconds of overhead. A stored procedure executing ten statements incurs one network round trip instead of ten, reducing total execution time significantly. The performance gain increases with network latency and statement count.

Query execution plans for stored procedures compile once and cache for reuse. The database parses SQL, optimizes the execution plan, and stores the compiled procedure. Subsequent executions skip parsing and optimization, directly executing the cached plan. This compilation advantage diminishes for statements executed infrequently, where the overhead of compiling occurs anyway.

Data transfer volume affects performance when applications fetch large result sets for processing. A stored procedure that filters and aggregates a million rows returns a few summary values to the application. The equivalent application code fetches all million rows over the network, processes them in memory, and discards most data. The stored procedure approach minimizes network transfer and memory usage.

-- Stored procedure reducing data transfer
CREATE FUNCTION get_sales_summary(p_start_date DATE, p_end_date DATE)
RETURNS TABLE (
  category VARCHAR,
  total_sales DECIMAL,
  average_order DECIMAL,
  order_count INT
)
AS $$
  SELECT 
    category,
    SUM(total) as total_sales,
    AVG(total) as average_order,
    COUNT(*) as order_count
  FROM orders o
  JOIN order_items oi ON o.id = oi.order_id
  JOIN products p ON oi.product_id = p.id
  WHERE o.created_at BETWEEN p_start_date AND p_end_date
  GROUP BY category
  ORDER BY total_sales DESC;
$$ LANGUAGE sql;

Parameter binding optimization occurs when prepared statements reuse execution plans with different parameter values. Stored procedures benefit from this optimization automatically. Applications using raw SQL must explicitly prepare statements to achieve similar performance. The database matches parameter types and can use indexes effectively when procedures use parameterized queries internally.

Lock contention increases when stored procedures hold locks for extended periods. A long-running procedure executing multiple statements within a transaction locks all affected rows until completion. Concurrent operations block waiting for lock release. Short procedures that complete quickly release locks sooner, improving concurrency. Breaking complex procedures into smaller units can reduce lock duration at the cost of additional round trips.

Memory consumption in stored procedures depends on temporary table usage, variable allocation, and result set buffering. Procedures that create large temporary tables consume memory proportional to data size. Cursor operations may hold entire result sets in memory. Database configuration limits memory available to individual connections, potentially causing out-of-memory errors for memory-intensive procedures.

-- Memory-efficient cursor processing
CREATE PROCEDURE process_large_dataset()
BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE v_id INT;
  DECLARE v_data VARCHAR;
  
  DECLARE cur CURSOR FOR
    SELECT id, data FROM large_table WHERE processed = 0;
  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  OPEN cur;
  
  read_loop: LOOP
    FETCH cur INTO v_id, v_data;
    IF done THEN
      LEAVE read_loop;
    END IF;
    
    -- Process one row at a time
    CALL process_single_row(v_id, v_data);
  END LOOP;
  
  CLOSE cur;
END;

Index usage within stored procedures follows the same optimization rules as standalone queries. The query optimizer analyzes each statement in the procedure and selects appropriate indexes. Poorly written procedures that bypass indexes or force table scans perform badly regardless of network optimization. Examining execution plans for statements within procedures reveals optimization opportunities.

Connection pooling behavior changes with stored procedures. Long-running procedures hold database connections from the pool, potentially exhausting available connections. Applications waiting for connections block until procedures complete. Designing procedures to execute quickly and release connections promptly maintains pool availability. Splitting batch operations across multiple procedure calls distributes load and prevents connection starvation.

Parallel execution within stored procedures depends on database capabilities. PostgreSQL can parallelize certain operations within a single query, but procedural logic executes sequentially. Multiple procedure calls from different connections can execute in parallel, utilizing multiple CPU cores. Database configuration controls parallelism and resource allocation for concurrent procedure execution.

Common Pitfalls

Missing output parameter handling causes silent failures in Ruby applications. A stored procedure with OUT parameters returns those values, but the calling code must explicitly retrieve them. Ignoring output parameters means losing important return values like generated IDs, counts, or status codes. Different database systems and gems handle output parameters differently.

# Incorrect: Ignoring output parameters
def process_order_wrong(customer_id)
  ActiveRecord::Base.connection.execute(
    "CALL create_order(#{customer_id}, @order_id)"
  )
  # @order_id is lost - not returned to Ruby
end

# Correct: Retrieving output parameters (MySQL)
def process_order_correct(customer_id)
  conn = Mysql2::Client.new(...)
  conn.query("CALL create_order(#{customer_id}, @order_id)")
  result = conn.query("SELECT @order_id as order_id")
  result.first['order_id']
end

SQL injection vulnerabilities persist even with stored procedures when parameter concatenation occurs. A stored procedure that builds dynamic SQL using string concatenation remains vulnerable. The Ruby application must still use parameterized queries when calling procedures, but the procedure implementation must also avoid SQL injection.

-- Vulnerable stored procedure
CREATE PROCEDURE search_orders_vulnerable(IN p_search_term VARCHAR)
BEGIN
  SET @sql = CONCAT('SELECT * FROM orders WHERE customer_name LIKE ''%', 
                    p_search_term, '%''');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END;

-- Safe stored procedure
CREATE PROCEDURE search_orders_safe(IN p_search_term VARCHAR)
BEGIN
  SELECT * FROM orders 
  WHERE customer_name LIKE CONCAT('%', p_search_term, '%');
END;

Transaction scope confusion occurs when applications assume stored procedures manage transactions independently. A procedure starting a transaction affects the application's transaction state. Calling a procedure within an application transaction creates nested transaction behavior, which some databases don't support. Understanding transaction boundaries prevents unexpected rollbacks or commits.

# Problematic: Nested transaction confusion
ActiveRecord::Base.transaction do
  customer = Customer.create!(name: 'John')
  
  # This procedure starts its own transaction
  # Behavior varies by database - might cause errors
  ActiveRecord::Base.connection.execute(
    "CALL process_with_transaction(#{customer.id})"
  )
  
  # If procedure commits, changes persist even if outer transaction rolls back
end

# Better: Let application manage transactions
ActiveRecord::Base.transaction do
  customer = Customer.create!(name: 'John')
  
  # Procedure doesn't manage transactions
  ActiveRecord::Base.connection.execute(
    "CALL process_without_transaction(#{customer.id})"
  )
end

Multiple result set handling fails when Ruby code doesn't process all result sets from a stored procedure. MySQL procedures that execute multiple SELECT statements return multiple result sets. The client must retrieve each result set to completion before executing additional queries. Failing to retrieve all result sets causes "Commands out of sync" errors.

# Incorrect: Not processing all result sets
def call_multi_result_procedure
  client = Mysql2::Client.new(...)
  client.query("CALL get_multiple_results()")
  # Missing next_result calls - causes errors
  client.query("SELECT 1") # ERROR: Commands out of sync
end

# Correct: Processing all result sets
def call_multi_result_procedure_correct
  client = Mysql2::Client.new(flags: Mysql2::Client::MULTI_RESULTS)
  client.query("CALL get_multiple_results()")
  
  # Process first result
  result = client.store_result
  
  # Process remaining results
  while client.next_result
    client.store_result
  end
  
  # Now safe to execute new queries
  client.query("SELECT 1")
end

Error handling gaps appear when applications don't catch database-specific exceptions from stored procedures. A procedure raising an error generates a database-specific exception. Generic exception handling masks the actual error cause. Specific error handling based on SQL state codes enables appropriate recovery strategies.

Type conversion mismatches cause subtle bugs when Ruby interprets stored procedure results incorrectly. Database decimal values may return as strings, requiring explicit conversion. Date and time handling varies between databases and Ruby. JSON columns need parsing after retrieval. Missing type conversion leads to calculation errors and comparison failures.

# Problematic: No type conversion
def calculate_total_wrong
  result = ActiveRecord::Base.connection.execute(
    "SELECT calculate_order_total(123)"
  )
  total = result.first['calculate_order_total']
  # total is a string, not a number
  total + 10 # String concatenation, not addition
end

# Correct: Explicit type conversion
def calculate_total_correct
  result = ActiveRecord::Base.connection.execute(
    "SELECT calculate_order_total(123) as total"
  )
  total = result.first['total'].to_f
  total + 10 # Numeric addition
end

Database connection leaks occur when procedures hold connections without proper cleanup. A procedure call that fails mid-execution may leave the connection in an inconsistent state. Transaction rollback may not happen automatically. Proper exception handling with connection cleanup prevents resource leaks.

Procedure redefinition during application runtime causes version skew. If a database migration updates a stored procedure while application instances run, different instances may call different procedure versions. Coordinating deployments to update procedures and application code simultaneously prevents inconsistent behavior. Blue-green deployments handle this more safely.

Reference

Common Stored Procedure Operations

Parameter Modes

Ruby Database Gem Comparison

Transaction Control in Procedures

Error Handling Patterns

Common SQL States

Performance Optimization Checklist

Security Considerations