Deterministic Queries & Stored Procedures for AI

SELECT * FROM sales
WHERE quarter = 'Q4'

SELECT s.*, c.name, c.email, c.ssn
FROM sales s
JOIN customers c ON s.customer_id = c.id
WHERE s.sale_date BETWEEN '2024-10-01' AND '2024-12-31'

CREATE PROCEDURE [dbo].[GetQuarterlyMetrics]
    @Quarter INT,
    @Year INT
AS
BEGIN
    -- Aggregated metrics only - no individual records
    SELECT
        Region,
        COUNT(*) AS TotalSales,
        SUM(Amount) AS TotalRevenue,
        AVG(Amount) AS AverageOrderValue,
        COUNT(DISTINCT CustomerId) AS UniqueCustomers
    FROM Sales
    WHERE Quarter = @Quarter AND Year = @Year
    GROUP BY Region
    ORDER BY TotalRevenue DESC;
END

# 1. Get metrics from stored procedure
metrics = call_procedure("GetQuarterlyMetrics", {"Quarter": 4, "Year": 2024})

# 2. Send ONLY the aggregated data to AI
prompt = f"""Analyze these quarterly sales metrics and provide insights:
{json.dumps(metrics, indent=2)}

Focus on: trends, top performers, and recommendations."""

# 3. AI generates report from safe, aggregated data
report = ai_model.generate(prompt)

{
  "available_procedures": [
    {
      "name": "GetSalesByRegion",
      "description": "Returns aggregated sales data by geographic region",
      "parameters": ["StartDate", "EndDate", "Region"],
      "use_when": "User asks about regional performance or geographic analysis"
    },
    {
      "name": "GetProductPerformance",
      "description": "Returns product category performance metrics",
      "parameters": ["Category", "TimePeriod"],
      "use_when": "User asks about product sales or category analysis"
    },
    {
      "name": "GetCustomerSegments",
      "description": "Returns anonymized customer segment statistics",
      "parameters": ["SegmentType"],
      "use_when": "User asks about customer demographics or segments"
    }
  ]
}

def ai_select_procedure(user_query, catalog):
    """
    AI selects the most appropriate procedure from the catalog.
    It can ONLY choose from predefined, approved procedures.
    """
    prompt = f"""Given this user query: "{user_query}"

Select the most appropriate procedure from this catalog:
{json.dumps(catalog, indent=2)}

Return ONLY a JSON object with:
- procedure_name: the name of the selected procedure
- parameters: the parameter values extracted from the query
"""

    # AI returns structured selection
    selection = ai_model.generate(prompt, response_format="json")

    # Validate the selection is in our catalog
    if selection["procedure_name"] not in [p["name"] for p in catalog]:
        raise ValueError("AI selected invalid procedure")

    return selection

def extract_parameters(user_query):
    """
    AI extracts structured parameters from natural language.
    It does NOT construct SQL - just identifies parameter values.
    """
    prompt = f"""Extract parameters from this query: "{user_query}"

Return JSON with these fields (use null if not specified):
- region: geographic region (North, South, East, West, or null for all)
- start_date: start date in YYYY-MM-DD format
- end_date: end date in YYYY-MM-DD format
- category: product category if mentioned

Example output: {{"region": "West", "start_date": "2024-10-01", "end_date": "2024-12-31"}}
"""

    params = ai_model.generate(prompt, response_format="json")

    # Validate and sanitize parameters
    return validate_parameters(params)

def validate_parameters(params):
    """Ensure parameters are safe before use"""
    valid_regions = ["North", "South", "East", "West", None]

    if params.get("region") not in valid_regions:
        params["region"] = None

    # Validate dates are in correct format
    for date_field in ["start_date", "end_date"]:
        if params.get(date_field):
            try:
                datetime.strptime(params[date_field], "%Y-%m-%d")
            except:
                params[date_field] = None

    return params

CREATE PROCEDURE [dbo].[GetCustomerOrderSummary]
    @CustomerId INT,
    @RequestingUser NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. AUDIT: Log every access attempt
    INSERT INTO SecurityAudit (ProcName, CustomerId, RequestedBy, RequestTime, IPAddress)
    SELECT 'GetCustomerOrderSummary', @CustomerId, @RequestingUser, GETDATE(), NULL;

    -- 2. AUTHORIZATION: Verify user can access this customer
    IF NOT EXISTS (
        SELECT 1 FROM UserCustomerAccess
        WHERE Username = @RequestingUser
        AND CustomerId = @CustomerId
    )
    BEGIN
        -- Log the unauthorized attempt
        UPDATE SecurityAudit
        SET AccessDenied = 1, DenialReason = 'User not authorized for customer'
        WHERE AuditId = SCOPE_IDENTITY();

        RAISERROR('Access denied', 16, 1);
        RETURN;
    END

    -- 3. DATA RETRIEVAL: Return only approved fields
    SELECT TOP 100  -- Limit results
        o.OrderId,
        o.OrderDate,
        o.TotalAmount,
        o.Status,
        -- Mask sensitive data
        CONCAT('****-****-****-', RIGHT(o.PaymentLast4, 4)) AS PaymentMethod,
        -- Include aggregated metrics
        (
            SELECT COUNT(*) FROM OrderItems oi
            WHERE oi.OrderId = o.OrderId
        ) AS ItemCount
    FROM Orders o
    WHERE o.CustomerId = @CustomerId
      AND o.Status != 'Cancelled'  -- Don't expose cancelled orders
    ORDER BY o.OrderDate DESC;
END

// ❌ WRONG: Mixing business params with AI params
{
  "model": "gpt-4o-mini",
  "messages": [...],
  "username": "john.doe",  // OpenAI rejects this!
  "department": "sales"    // OpenAI rejects this too!
}

# ✓ CORRECT: Separate business logic from AI payload

# 1. Business parameters go to the STORED PROCEDURE
proc_params = {
    "username": "john.doe",
    "department": "sales",
    "record_id": 12345
}

# 2. Call stored procedure with business params
data = call_procedure("GetRecordData", proc_params)

# 3. AI payload contains ONLY AI-standard parameters
ai_payload = {
    "model": "gpt-4o-mini",
    "messages": [
        {"role": "system", "content": "You are an analyst."},
        {"role": "user", "content": f"Summarize: {data}"}
    ],
    "max_tokens": 500,
    "temperature": 0.3
}

# Now you can switch AI providers without code changes!