How to Create a Generic Stored Procedure for KPI Calculation (SQL + AWS Lambda)

In modern data engineering, building scalable and reusable systems is essential. A generic stored procedure dynamic SQL approach is one of the most powerful patterns available to data engineers today — it eliminates redundancy, reduces maintenance overhead, and keeps your KPI calculation logic centralized. Without this pattern, writing separate SQL queries for every KPI quickly becomes messy and hard to maintain.

A better approach?
👉 Use a Generic Stored Procedure powered by Dynamic SQL, and trigger it using AWS Lambda. Furthermore, by combining these technologies, teams can build enterprise-grade KPI systems that scale without ever touching the core procedure again.

In this blog, engineers can learn:

What a generic stored procedure is
Why dynamic SQL is important
Step-by-step KPI implementation
How to trigger it using AWS Lambda

What is a Generic Stored Procedure?

A generic stored procedure is a reusable database program that works dynamically based on input parameters. In other words, instead of hardcoding logic for each metric, teams can define the logic once and reuse it across countless use cases.

For example, instead of writing multiple queries like:

Total Revenue
Total Orders
Average Order Value

With a generic stored procedure, engineers can create one procedure and pass inputs like:

Aggregation function (SUM, COUNT, AVG)
Column name
Table name
Conditions

Use Case: KPI Calculation with Dynamic SQL

To understand how this works in practice, let’s assume we have a simple table:

			
sales_data (
    order_id INT,
    customer_id INT,
    amount DECIMAL,
    order_date DATE,
    region VARCHAR
)

		

Using this table, teams can calculate:

Total Revenue
Total Orders
Revenue by Region

Step 1: Basic Stored Procedure (Static Approach)

			
CREATE OR REPLACE PROCEDURE calculate_kpi(
    kpi_name TEXT,
    start_date DATE,
    end_date DATE
)
LANGUAGE plpgsql
AS $$
DECLARE
    result NUMERIC;
BEGIN    IF kpi_name = 'TOTAL_REVENUE' THEN
        SELECT SUM(amount)
        INTO result
        FROM sales_data
        WHERE order_date BETWEEN start_date AND end_date;    ELSIF kpi_name = 'TOTAL_ORDERS' THEN
        SELECT COUNT(*)
        INTO result
        FROM sales_data
        WHERE order_date BETWEEN start_date AND end_date;    ELSE
        RAISE EXCEPTION 'Invalid KPI';
    END IF;    RAISE NOTICE 'Result: %', result;END;
$$;

		

Problem ❌

Not scalable
Every new KPI requires code change
Hard to maintain

Step 2: Dynamic SQL — The Generic Approach

This is where the real power of generic stored procedure dynamic SQL comes in 🚀 Rather than branching logic for every KPI, the procedure builds and executes a query at runtime based on the parameters passed in. As a result, one procedure can serve unlimited KPI calculation needs — no code changes required.

			
CREATE OR REPLACE PROCEDURE dynamic_kpi(
    agg_function TEXT,
    column_name TEXT,
    table_name TEXT,
    condition TEXT
)
LANGUAGE plpgsql
AS $$
DECLARE
    query TEXT;
    result NUMERIC;
BEGIN    query := 'SELECT ' || agg_function || '(' || column_name || ') 
              FROM ' || table_name || ' 
              WHERE ' || condition;    EXECUTE query INTO result;    RAISE NOTICE 'KPI Result: %', result;END;
$$;

		

Why Dynamic SQL Powers a Generic Stored Procedure

Dynamic SQL allows queries to be built at runtime, making the generic stored procedure dynamic SQL pattern fully flexible. Consequently, the same procedure can calculate revenue, count orders, or compute averages — all without a single code change to the procedure itself.

Without Dynamic SQL ❌

10 KPIs → 10 stored procedures
Frequent deployments
Duplicate logic

With Dynamic SQL ✅

1 procedure → unlimited KPIs
No code changes needed
Fully reusable

Example Execution

			
CALL dynamic_kpi(
    'SUM',
    'amount',
    'sales_data',
    'order_date BETWEEN ''2024-01-01'' AND ''2024-12-31'''
);CALL dynamic_kpi(
    'COUNT',
    'order_id',
    'sales_data',
    'region = ''APAC'''
);

		

Step 3: Make It Enterprise-Ready — Metadata-Driven KPI Calculation

To take the generic stored procedure dynamic SQL pattern even further, teams can introduce a configuration (metadata) table. In other words, this approach drives all KPI logic from data, not code. Create a config table:

			
kpi_config (
    kpi_name TEXT,
    agg_function TEXT,
    column_name TEXT,
    table_name TEXT,
    condition TEXT
)

		

With this setup, the procedure reads from this table and executes dynamically — no hardcoded logic required. Furthermore, adding a new KPI is as simple as inserting a new row.

👉 This is how real-world data platforms work. In addition, a metadata-driven design makes onboarding new KPIs completely seamless for any team.

Step 4: Architecture with AWS Lambda and Dynamic SQL

Client/API → API Gateway → Lambda → PostgreSQL → Stored Procedure

Step 5: Lambda Function (Python)

			
import psycopg2
import osdef lambda_handler(event, context):    conn = psycopg2.connect(
        host=os.environ['DB_HOST'],
        database=os.environ['DB_NAME'],
        user=os.environ['DB_USER'],
        password=os.environ['DB_PASSWORD'],
        port=5432
    )    cursor = conn.cursor()    query = f"""
    CALL dynamic_kpi(
        '{event['agg_function']}',
        '{event['column_name']}',
        '{event['table_name']}',
        '{event['condition']}'
    )
    """    cursor.execute(query)
    conn.commit()    cursor.close()
    conn.close()    return {
        "statusCode": 200,
        "body": "KPI executed successfully"
    }

		

Step 6: Trigger Lambda for KPI Calculation

1. API Gateway (Real-Time KPI)

			
{
  "agg_function": "SUM",
  "column_name": "amount",
  "table_name": "sales_data",
  "condition": "order_date >= CURRENT_DATE - INTERVAL '1 day'"
}

		

2. Scheduler (Automated KPI)

Daily KPI refresh
Weekly reports

3. Event-Based

Trigger when new data lands in S3
Trigger after ETL pipeline

Security Best Practices ⚠️

Dynamic SQL is powerful — but it also carries risk. Therefore, any generic stored procedure dynamic SQL implementation must take SQL injection prevention seriously.

Avoid SQL Injection:

To guard against injection attacks, teams can use PostgreSQL format():

			
query := format(
    'SELECT %I(%I) FROM %I WHERE %s',
    agg_function,
    column_name,
    table_name,
    condition
);

		

Additional Tips:

Validate inputs (whitelist functions like SUM, COUNT)
Use AWS Secrets Manager for credentials
Add logging in Lambda

Real-World Enhancements

The generic stored procedure dynamic SQL pattern is highly extensible. With that in mind, teams can build on this foundation to:

Store KPI results in a table
Build dashboards (Power BI / Tableau)
Create REST APIs for KPI access
Integrate with Databricks or data lakes

Final Thoughts

A generic stored procedure dynamic SQL architecture, triggered via AWS Lambda, is one of the most effective design patterns for modern data platforms. To summarize, this approach centralizes all KPI calculation logic into a single, reusable procedure — eliminating duplication and enabling teams to scale analytics without writing new code.

In contrast to fragile, query-per-KPI approaches, this generic stored procedure dynamic SQL pattern helps teams:

Reduce duplicate SQL
Build scalable KPI systems
Enable real-time and automated analytics

👉 If you are building production-grade data pipelines, adopting a generic stored procedure dynamic SQL strategy is a must-have in your engineering toolkit. Start with the pattern shown here, extend it with metadata-driven configuration, and connect it to AWS Lambda for fully automated, scalable KPI calculation. Try it on your next project and see the difference.

Srinimf

Subscribe for DAILY TIPS