How to Debug Stored Procedures with AI

A procedure fails differently than a query

A query fails the same way every time. A procedure fails for one caller, on one day, with one parameter — because it contains branches, and each branch is a different program.

So the first question is never "what is wrong with this SQL". It is: which path did this execution take?

Isolate the failing statement

A procedure is rarely slow or broken as a whole; one statement inside it is. Find that first.

• SQL Server — SET STATISTICS IO ON and the actual execution plan show cost per statement. PRINT or a debug table between steps gives you row counts.
• Oracle — DBMS_OUTPUT.PUT_LINE between steps, and DBMS_PROFILER when you need line-level timings.
• MySQL — SELECT into a debug table between steps; the profiler is limited for routines.

Log the parameter values at entry too. Half of "cannot reproduce" turns out to be a parameter nobody expected.

Parameter sniffing

The signature symptom: the procedure is fast for one customer and slow for another, and running the same statement by hand is always fast.

SQL Server compiles a plan on first execution using the parameters it saw, then reuses it. A plan built for a customer with 20 orders is a poor plan for a customer with two million. The usual answers:

-- recompile just the statement that is sensitive
SELECT ... FROM orders WHERE customer_id = @customer_id
OPTION (RECOMPILE);

-- or break the sniffing by copying into a local variable
DECLARE @cid INT = @customer_id;
SELECT ... FROM orders WHERE customer_id = @cid;

Both trade a little compile time for a plan that fits the actual input.

Transactions and error handling

Two failure modes that produce data problems rather than error messages:

• A TRY/CATCH that logs and continues. The caller sees success, half the work was rolled back. Any CATCH without a ROLLBACK or a re-thrown error deserves suspicion.
• Nested transactions. In SQL Server, an inner COMMIT does not commit — it decrements a counter. A procedure that assumes it owns the transaction behaves differently when called inside another one.

NULL parameters

WHERE customer_id = @customer_id matches nothing when the parameter is NULL, including rows where the column is NULL. Procedures that "return no rows for some inputs" are usually this. The intent is normally:

WHERE (@customer_id IS NULL OR customer_id = @customer_id)

which also makes the procedure a candidate for OPTION (RECOMPILE), since the optimal plan differs by parameter.

Using AI on a procedure

Paste the procedure into the box at the top of this page to get a walkthrough: what each branch does, where the transaction starts and ends, which statements can fail and what the error handler does about it. That is a map of the code, and it is the part that takes longest by hand.

If you have an error message, paste it with the procedure — the code narrows the diagnosis to a rule instead of a search. If the problem is speed rather than failure, isolate the statement first and optimise that statement on its own; tuning a whole procedure at once rarely converges.

Before you ship the change

1. Find every caller and the parameters they pass.
2. Save a baseline result for a closed period.
3. Change one statement. Compare row counts first, values second.
4. Keep the previous version in a comment with the date and the reason.