The AI That Writes Your Stored Procedures (Better Than You Can)
Your company has 2,347 stored procedures. Some are over 1,000 lines long. The original authors left years ago. Every change is a gamble. Performance is terrible because no one knows which indexes are actually used. A single bug in a stored procedure once caused a 45‑minute outage. The team is terrified to touch them, so they accumulate technical debt like interest on a credit card.
If this sounds familiar, you are not alone. Stored procedures are the silent graveyard of database business logic. They are powerful – running close to the data, reducing network round trips, enforcing security – but they are also notoriously hard to maintain. Human‑written stored procedures suffer from inconsistent error handling, missing transaction boundaries, SQL injection vulnerabilities, and non‑existent documentation.
Generative AI changes this paradigm. Modern LLMs (GPT‑4, Code Llama, Claude) can write stored procedures from natural language specifications, refactor legacy code, add error handling, and even rewrite slow procedures to be 10x faster. This article dives into the technical capabilities of AI‑driven stored procedure generation, compares it to traditional development, and provides a practical roadmap for adopting AI as your database logic co‑pilot.
Definition: AI‑generated stored procedures are SQL routines (functions, procedures, triggers) produced by large language models from high‑level descriptions or legacy code, with the ability to optimise, refactor, and document automatically.
The Spaghetti‑Code Tax: Why Manual Stored Procedures Fail at Scale
To appreciate AI code generation, first understand why human‑written stored procedures become unmaintainable:
- No modularity: Hundreds of procedures repeat the same logic (e.g., date formatting, permission checks). A bug in one copy propagates everywhere.
- Poor error handling: Most procedures assume success. No `TRY/CATCH`, no `ROLLBACK` on error. Corruption spreads silently.
- SQL injection risks: Dynamic SQL built with string concatenation (even accidentally) opens doors for injection.
- Performance regressions: A procedure that worked for 1,000 rows may scan a million rows after data growth. No one notices until pager goes off.
- Zero documentation: The procedure name is the only hint. Parameters have no descriptions. Side effects are hidden.
A 2025 study of 500 enterprise databases found that 74% of stored procedures had at least one significant code smell (duplicate code, missing error handling, risky dynamic SQL). The average time to understand a new stored procedure was 47 minutes, and the median time to fix a bug was 6 hours – often longer than rewriting from scratch.
- Natural language to stored procedure – Describe business logic in English; AI generates correct SQL with parameters, error handling, and transactions.
- Legacy refactoring – AI analyses existing procedures, extracts common logic into helper functions, and adds documentation.
- Automatic optimisation – Rewrites slow set‑based operations, adds missing indexes, and suggests partitioning.
- Security hardening – Converts risky dynamic SQL to parameterised queries, adds input validation, and enforces row‑level security.
- Unit test generation – Creates test cases for boundary conditions, edge inputs, and expected exceptions.
- Cross‑database translation – Convert stored procedures between PostgreSQL, MySQL, SQL Server, Oracle.
- Production case studies – Real examples of AI‑generated procedures reducing bugs by 90% and development time by 80%.
How LLMs Generate Correct Stored Procedures
Generating stored procedures that actually run and are correct is harder than generating ad‑hoc SQL. A stored procedure has parameters, control flow (loops, conditionals), transaction semantics, and error handling. Modern LLMs overcome this with specialised prompting and fine‑tuning.
1. Schema‑Aware Prompting
The LLM receives the database schema (tables, columns, data types, constraints, indexes) as part of the prompt. This allows it to generate column names, avoid typos, and respect foreign keys. For example:
-- User request: "Write a procedure to transfer money between accounts."
-- Schema given to LLM:
CREATE TABLE accounts (id INT PRIMARY KEY, balance DECIMAL(10,2), owner_id INT);
CREATE TABLE transactions (id INT PRIMARY KEY, from_account INT, to_account INT, amount DECIMAL(10,2), created_at TIMESTAMP);
-- Foreign key: transactions.from_account → accounts.idThe LLM will generate a procedure that updates balances and inserts a transaction record, with proper locking (`FOR UPDATE`) to avoid race conditions.
2. Chain‑of‑Thought Reasoning
The AI decomposes the request into steps: lock accounts, check sufficient balance, update both accounts, insert transaction row, commit. It then translates each step into SQL statements. This stepwise reasoning reduces hallucinations and ensures all constraints are satisfied.
-- Example AI‑generated procedure (PostgreSQL)
CREATE OR REPLACE PROCEDURE transfer_money(
p_from_account INT,
p_to_account INT,
p_amount DECIMAL(10,2),
p_comment TEXT DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
v_from_balance DECIMAL(10,2);
v_to_balance DECIMAL(10,2);
BEGIN
-- Lock both accounts in a consistent order to avoid deadlocks
PERFORM 1 FROM accounts WHERE id = p_from_account FOR UPDATE;
PERFORM 1 FROM accounts WHERE id = p_to_account FOR UPDATE;
-- Check balance
SELECT balance INTO v_from_balance FROM accounts WHERE id = p_from_account;
IF v_from_balance < p_amount THEN
RAISE EXCEPTION 'Insufficient balance in account %', p_from_account;
END IF;
-- Perform transfer
UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_account;
UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_account;
-- Record transaction
INSERT INTO transactions (from_account, to_account, amount, created_at, comment)
VALUES (p_from_account, p_to_account, p_amount, NOW(), p_comment);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
$$;Note the error handling, locking, and explicit commit – hallmarks of a production‑grade procedure that many human developers forget.
3. Fine‑Tuned Models for SQL
General‑purpose LLMs (GPT‑4, Claude) work well, but fine‑tuned models (e.g., `defog/sqlcoder`, `togetherai/llama-2-7b-sql`) achieve higher accuracy with lower latency. The ebook provides scripts to fine‑tune Code Llama on your own stored procedure corpus – capturing your naming conventions, error handling patterns, and security requirements.
Refactoring Legacy Procedures: From 1,000 Lines to 100 Lines
AI is not just for greenfield development. It excels at understanding and refactoring legacy code. For example, a typical legacy stored procedure might contain:
- Copy‑pasted logic repeated for each of five statuses.
- Cursor loops that could be replaced by set‑based updates.
- Missing `COMMIT` and `ROLLBACK`, causing transactions to leak.
- No parameter validation, leading to silent integer overflows.
When you feed this legacy code to an LLM with the instruction "Refactor to use set‑based operations, add error handling, and extract common logic into helper procedures", the AI produces a much cleaner result. In one real case (detailed in the ebook), a 1,200‑line Oracle procedure was reduced to 180 lines of PostgreSQL, with a 15x performance improvement. The AI also identified two missing foreign keys and a subtle concurrency bug that had been causing occasional duplicates for years.
To achieve this, the AI analyses the control flow graph, identifies patterns (e.g., repeated `IF status = 'X' THEN ...`), and suggests abstractions. It also detects dead code – branches that are never executed because of contradictory conditions.
Automatic Optimisation: Making Slow Procedures Fast
One of the most valuable capabilities of AI code generation is performance optimisation. The AI can:
- Replace row‑by‑row cursors with bulk `INSERT ... SELECT`.
- Add `WITH` (CTE) clauses to break complex queries into readable steps.
- Suggest missing indexes based on `WHERE` clauses and `JOIN` conditions.
- Rewrite `OR` conditions into `UNION` when beneficial.
- Add query hints (e.g., `FORCE INDEX`) for stubborn optimisers.
The AI learns optimisation rules from proven patterns (e.g., "avoid functions in `WHERE` clauses", "use `EXISTS` instead of `IN` for large subqueries"). It also uses execution plan feedback – if the generated procedure is slow in testing, the AI can be asked to revise it based on the `EXPLAIN` output.
-- Before: slow cursor loop
DECLARE cur CURSOR FOR SELECT id FROM orders WHERE status = 'pending';
FOR rec IN cur LOOP
UPDATE orders SET processed_at = NOW() WHERE id = rec.id;
END LOOP;
-- After AI optimisation
UPDATE orders SET processed_at = NOW() WHERE status = 'pending';In a benchmark with 10 legacy stored procedures, AI‑driven optimisation reduced average execution time by 87% while making the code 70% shorter. The AI also added comments explaining the optimisation choices, turning the code into documentation.
Case Study: Logistics Company Saves 40 Hours/Week
A logistics company had a stored procedure that processed incoming shipment events. It took 8 seconds per event because of nested cursors. After feeding the procedure to an LLM with the prompt "Optimise for batch processing", the AI rewrote it to process 1,000 events in a single `UPDATE` with a `CASE` statement. Processing time dropped to 120ms per batch. The company saved 40 hours of database CPU time per week, allowing them to shut down two replicas.
Implementing AI‑Driven Stored Procedure Development
The ebook Database Management Using AI provides a practical framework for integrating AI into your database development workflow:
- Schema extraction: Use database introspection tools (e.g., `pg_dump --schema-only`, `SHOW CREATE TABLE`) to capture schema definitions. Store them in a format the LLM can consume.
- Prompt library: Create reusable prompt templates for common tasks: new procedure generation, refactoring, optimisation, security hardening. Include examples specific to your domain.
- Validation sandbox: Run generated procedures on a read‑only clone or a test database. Compare output with expected results. Use `EXPLAIN` to detect performance regressions.
- Human review gate: For critical procedures (e.g., financial transactions), require a senior DBA to review and approve before production deployment.
- Feedback loop: When the AI generates a wrong or inefficient procedure, correct it and add the correction to the fine‑tuning dataset. Over time, the model learns your organisation's specific patterns.
Many teams start with a pilot: ask the AI to generate a new procedure, compare it to the manually written version, and run performance tests. The results are often surprising – the AI version is frequently cleaner and faster.
Get “Database Management Using AI” on Amazon → Get on Google Play →
Advanced Techniques: Unit Test Generation and Documentation
Beyond writing the procedure itself, AI can generate comprehensive unit tests. Given a procedure signature and a few example inputs, the LLM produces test cases for boundary conditions, null values, empty sets, and expected exceptions. These tests can be executed using frameworks like `pgTAP` (PostgreSQL) or `tSQLt` (SQL Server).
-- AI‑generated test for transfer_money
BEGIN;
SELECT plan(5);
-- Test normal transfer
SELECT transfer_money(1, 2, 100.00);
SELECT results_eq('SELECT balance FROM accounts WHERE id=1', ARRAY[900.00], 'From account debited');
SELECT results_eq('SELECT balance FROM accounts WHERE id=2', ARRAY[1100.00], 'To account credited');
-- Test insufficient funds
SELECT throws_ok('CALL transfer_money(1, 2, 10000.00)', 'Insufficient balance', 'Insufficient funds error');
-- Test same account
SELECT throws_ok('CALL transfer_money(1, 1, 10.00)', 'Cannot transfer to same account');
ROLLBACK;Documentation is also generated automatically: parameter descriptions, side effects (locks, audit logs), error messages, and examples. This documentation can be output as Markdown, HTML, or even embedded as comments in the procedure.
Security Hardening: AI as a SQL Injection Firewall
Dynamic SQL is the leading cause of injection vulnerabilities in stored procedures. AI can detect patterns where user input is concatenated into query strings and rewrite them to use parameters (`EXECUTE ... USING` in PostgreSQL, `sp_executesql` in SQL Server). It can also add input validation (e.g., `IF p_username ~ '^[a-zA-Z0-9_]+$' THEN ...`) and reject suspicious inputs.
In a security audit of 200 legacy procedures, an AI tool flagged 31 potential injection points; after rewriting, all were eliminated. The ebook includes a security‑hardening prompt that instructs the LLM to always prefer static SQL, use parameterised dynamic SQL when necessary, and add length/format validation.
Common Pitfalls and How to Avoid Them
- Schema drift: If the database schema changes after the AI generated the procedure, the procedure may break. Solution: Include schema version in the prompt and run a schema‑aware validation test.
- Hallucinated column names: LLMs sometimes invent columns. Solution: Use a schema‑aware linter that rejects SQL with unknown identifiers.
- Missing transaction boundaries: AI might generate procedures without explicit `BEGIN/COMMIT`. Solution: Use a prompt template that always includes transaction handling and reviews the output.
- Infinite loops: In PL/pgSQL or T‑SQL, loops without termination conditions can hang. Solution: Run procedures in a sandbox with a timeout, and ask the AI to rewrite any loop to be set‑based.
Observability and Trust
To trust AI‑generated stored procedures, you need observability. The ebook provides instrumentation that logs:
- Which procedures were generated or refactored by AI and when.
- The natural language prompt used.
- Human reviews and any corrections.
- Performance metrics before and after AI optimisation.
This audit trail satisfies compliance requirements (e.g., SOX) and helps improve the AI model over time.
No comments:
Post a Comment