Nobody volunteers to edit the procedure that fills an entire screen, then several more. Refactoring a long stored procedure starts with proof of what it returns, so a cleaner version does not quietly change the answer.
Save the Result Before Refactoring a Long Stored Procedure
I start by writing down the contract. List parameters, result sets, column types, ordering requirements, output parameters, return codes, and side effects. A procedure that updates rows and then selects a report has two contracts. Capturing only the report misses half the risk. Use a restored copy of representative data. Record several parameter combinations, including empty, NULL, and boundary inputs. If the procedure depends on the clock, isolate the date as an input for the comparison. Keep the original definition in a controlled script. The goal is a reproducible before state, not a screenshot of one lucky run.
For a single result set, put the old and new calls into tables with the same schema. Choose explicit columns and types. Do not use SELECT INTO as a shortcut if computed or nullable columns obscure the intended contract. If a procedure returns several result sets, capture each through a test harness that understands each shape. An application test around the procedure can be more useful than a single SQL comparison because it checks how callers consume those results.
CREATE TABLE #Before (CustomerID int, TotalDue decimal(19,4));
INSERT #Before (CustomerID, TotalDue)
EXEC dbo.ReportCustomerBalance @AsOfDate = '20260101';
SELECT CustomerID, TotalDue FROM #Before;Refactoring a Long Stored Procedure One Named Stage at a Time
I see the worst procedures use one temporary table for four unrelated jobs. Split the flow into stages named for their contents: eligible customers, posted charges, applied payments, and final balances. Define a clear key for every stage. Put a primary key or useful index on a temp table when its consumers need one. A common table expression is fine for a short single-use expression, but a temp table gives you a point to inspect and index when the flow gets complicated. Resist changing the algorithm and the shape at the same time. First make the existing work visible.
Write down the row grain beside each stage. Is one row a customer, an invoice, or an invoice line? That question catches accidental fan-out before it becomes a mysterious total. Run each stage with the same parameters used in the baseline. Inspect duplicates on its intended key. The names should help the next reader predict what a table contains without opening every join. That reader can be you next Tuesday.
Rename Variables for Meaning
Names such as @x, @flag2, and @tmp are cheap to type and expensive to debug. Rename them for the value they hold, not for the type alone. @CutoffDate says more than @Date1. @HasUnpostedCharges says more than @Bit. Keep variable scope close to the statement that needs it. If a variable is assigned in several branches, check whether those branches represent different concepts that deserve separate names. Search for every reference before changing one.
Parameters deserve the same care. If public callers depend on parameter names, keep those names until you can coordinate a contract change. Internal cleanup does not give permission to break named calls. Check dynamic SQL for parameter names embedded in strings, too. A text search of the procedure definition is a start; execution of each branch is the real check.
Remove Dead Branches With Evidence
An old comment that says "unused" is not evidence. Find callers, inspect the branch condition, and test whether supported inputs can reach it. Code coverage from application tests helps. Query Store can show whether a statement ran during the observation window, but absence there does not prove a branch is dead. Seasonal jobs and rare error paths exist. Move suspected dead code into a separate removal decision with an owner and a rollback path. I have seen a harmless-looking branch carry the only handling for a year-end edge case.
Delete one branch at a time after its behavior is understood. Removing stale comments and unreachable variables makes the procedure easier to read, but that is not the same as changing business rules. Keep the review narrow. If a branch is reachable but obsolete by policy, record that policy decision separately from the mechanical refactor.
Compare Both Directions
Capture the new version into #After with the same columns and parameters. I deploy it under a temporary name such as dbo.ReportCustomerBalance_Refactored until the check passes. EXCEPT compares distinct rows. Run it both ways: old minus new and new minus old. One direction alone can miss added rows. For duplicate-sensitive outputs, compare grouped rows and counts for each complete value tuple, since EXCEPT alone discards duplicates. Match data types explicitly. Pay attention to decimal scale, collation, trailing spaces, and NULL. If the application expects a stable order, add an explicit ORDER BY at the outermost result query and test it separately. Tables do not promise order because the rows looked sorted yesterday.
CREATE TABLE #After (CustomerID int, TotalDue decimal(19,4));
INSERT #After (CustomerID, TotalDue)
EXEC dbo.ReportCustomerBalance_Refactored @AsOfDate = '20260101';
SELECT CustomerID, TotalDue FROM #Before
EXCEPT
SELECT CustomerID, TotalDue FROM #After;
SELECT CustomerID, TotalDue FROM #After
EXCEPT
SELECT CustomerID, TotalDue FROM #Before;Test Side Effects When Refactoring a Long Stored Procedure
Result equality is only part of the check when a procedure writes data. Compare changed rows, audit entries, transaction behavior, and messages that callers use. Run a failure case inside a disposable database copy. Confirm that a failed statement does not leave an open transaction or half a workflow committed. Check output parameters and return values for every tested path. A procedure can return the same table while quietly changing a status flag. I check this before discussing speed because a faster wrong answer has limited charm.
Use SET STATISTICS IO and the actual plan after the behavior check passes. A refactor that materializes a stage can change estimates and tempdb work. Measure the old and new versions under comparable data, parameters, and cache conditions. Do not assume cleaner text means faster execution. Document any intentional performance tradeoff for the reviewer.
Release in Small, Reversible Steps
Keep the old definition and a deployment script that can restore it. Record dependencies and permissions before replacement. Test the deployment under the same SET options used in production. If the procedure owns multiple result sets, verify the application contract rather than relying on an SSMS grid. Run the baseline comparison again after the final edit, not only after an early draft. This last check catches a helpful cleanup added during review.
What should the reviewer see? A small map of stages, the before and after calls, both EXCEPT results, duplicate checks where needed, and the exact rollback script. The document need not be long. The procedure already handled that assignment. Clear evidence lets a second person approve a large cleanup without having to trust every changed line on sight.
Leave a Map for the Next Change
Put a brief comment above each stage explaining its input and output grain. Keep business rules near the predicate that applies them. If a rule has an awkward exception, name it plainly. A readable procedure is one in which a new maintainer can locate a rule and predict the impact of changing it. Review the final text once from top to bottom without looking at the diff. Does the data flow make sense? Does every temp table earn its place? That final reading is where the next unnecessary branch usually becomes obvious.
Store the comparison cases with the procedure's tests so the next edit starts with a baseline. Refactoring a long stored procedure the first time is expensive because nobody knows what the code promises. A small repeatable harness pays back that cost on the next change. I would rather inherit a long procedure with reliable tests than a short one whose behavior lives only in someone's memory.
Related reading on this blog: How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio and Capturing Stored Procedure Executions with Extended Events in SQL Server.
A shorter procedure is not a correct procedure, it is a claim the baseline must confirm.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
