There is a particular kind of stored procedure you find in systems that have been earning money for ten years or more. It is two thousand lines long. It has been edited by nine people. It has commented-out blocks dated 2014 that nobody dares delete. And somewhere inside it is the only correct definition of how your company calculates a commission.
Nobody wants to touch it. That is reasonable. But "nobody touches it" is not a strategy, because eventually somebody has to.
Why the logic ended up there
It is worth saying plainly: this is not incompetence. Putting logic in the database was often the right call at the time.
The data was already there, so the calculation was faster. Multiple applications — a web front end, a reporting tool, a nightly job, an Access database in accounts — all needed the same answer, and the database was the only thing they shared. Deploying a procedure change did not require an application release.
Those were good reasons. They are just not reasons that survive a decade of growth.
The real problem is not the T-SQL
The procedure being long is not the issue. The issue is that the rule exists in exactly one place, that place is hard to read, and nobody can tell you with confidence what it does without running it.
That produces three specific business risks:
- Nobody can answer questions about the rule. "Why did this invoice come out at this number?" becomes an investigation rather than an answer.
- The rule cannot be tested. Nothing verifies it, so a change is a leap of faith.
- The rule cannot be reused. When you want the same calculation in a mobile app or an API, you either duplicate it — and now you have two truths — or you route everything back through the database.
What we actually do
We do not start by rewriting it. Rewriting a procedure you do not understand is how you find out, three weeks later, that the ISNULL on line 1,400 was load-bearing.
Characterise first. Before changing anything, we capture what the procedure currently does for a wide range of real inputs, straight from production data. Not what it is supposed to do — what it does, including the behaviour that looks like a bug. That set of input/output pairs becomes the safety net.
Then describe it in English. We write down the rule as a business person would state it, and take it to someone who knows the business. This is where the interesting conversations happen. Very often you discover that two of the branches handle a customer type that no longer exists, or that a rule everyone believes is in force was silently overridden in 2019.
Then find the seam. Usually the procedure is doing three jobs at once: fetching data, applying rules, and shaping output. Those can be separated inside T-SQL first, without moving anything to C#. That alone makes it readable and testable.
Only then decide where it should live. Sometimes the answer is: nowhere else. A set-based calculation over millions of rows belongs in the database, and moving it to C# would be a downgrade. Sometimes the rule is genuinely application logic that ended up in SQL by accident, and it should move. The point is that this is now a decision with evidence behind it, rather than a preference.
A note on performance
While you are in there, you will find performance problems. Resist fixing them at the same time as changing behaviour. If you alter the logic and the indexing in one pass and the numbers come out different, you will not know which change caused it.
Characterise, refactor with identical output, verify, then optimise. It is slower and it is the only approach that lets you sleep.
What good looks like afterwards
You do not need the procedure gone. You need:
- The rule written down in a form the business can read and confirm.
- Tests that fail if the numbers change.
- One place that owns the calculation, callable from the web application, the API and the mobile app alike.
- A developer other than the original author able to change it without fear.
That is a very different position from where most systems start, and getting there rarely requires the rewrite people assume it does.