Stored Proc

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);
…

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
…

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,
…