Business logic in stored procedures: when it helps and when it hurts

Many long-running business systems have a secret. The real rules are not in the application at all. They are in the database, in a stored procedure with a name like "calculate discount v3 final", written by someone who left years ago.

Putting business logic in stored procedures is not always wrong. But it is worth knowing when it helps and when it quietly makes every change harder.

Why teams put business logic in stored procedures

There are good reasons it happens:

• Speed. The logic runs right next to the data, with no round trips.
• One place. Several applications can share the same procedure.
• Familiar skills. Plenty of teams are strongest in SQL.
• Set-based work. For bulk updates and reporting, SQL is hard to beat.

For data-heavy tasks such as reconciliation, bulk pricing updates or overnight jobs, stored procedures are often the right answer.

Where it starts to hurt

Nobody outside the database team can read it

Finance owns the discount policy. Credit owns the credit policy. Neither team can read T-SQL. So they have to trust that the procedure does what they asked, and they cannot check it themselves.

Every change is a deployment

Moving a discount threshold from 5% to 7.5% should take a minute. In a stored procedure, it means a script, a review, a test in a lower environment and a deployment window. The policy owner waits, and often works around the system in the meantime.

Numbers get hard-coded

Limits and thresholds tend to end up as literal values inside CASE statements. Over time you get many copies of the same number in different procedures, and nobody is sure which ones matter.

Testing is awkward

Unit testing stored procedures is possible but rarely done well. Most teams test by running the procedure against a copy of live data and eyeballing the result. That misses edge cases, and edge cases are where the arguments start.

It cannot explain itself

When someone asks why a customer got a certain price last March, a stored procedure cannot tell you which branch it took. Unless someone built careful logging, the answer is lost.

A simple test: data logic or decision logic?

Ask one question about each piece of logic: who owns the policy?

• If it is about the shape, integrity or movement of data, and IT owns it, a stored procedure is a fine home. Think constraints, joins, aggregations and batch jobs.
• If a business team owns it, changes it and gets asked about it, it is decision logic. It belongs somewhere that team can read, test and change.

Most legacy procedures mix the two. That is the root of the pain.

Pulling decision logic out, safely

You do not need a big-bang rewrite. A gradual approach works better.

1. Find the decisions. List the procedures that answer a business question: price, discount, approval, eligibility, risk.
2. Write the rules in plain English. Sit with the policy owner and agree what the logic should do. You will usually find at least one surprise.
3. Separate the numbers. Move thresholds, bands and lists into tables the business can maintain.
4. Rebuild the decision outside the database. Keep the data access where it is; move the decision.
5. Run both side by side. Compare answers on real cases before switching over.
6. Call the new decision from the old places. Applications ask the decision service instead of calling the procedure.

Where Condexa fits

Condexa is built on Microsoft .NET and SQL Server, so it is comfortable next to an existing SQL estate. A workflow can read your SQL Server tables directly through database lookups, and lookup tables can read a SQL Server table so reference data stays where it is. The decision itself becomes a visual workflow the policy owner can read.

You test with real examples and read the trace, which shows every step, every lookup and every value. When you publish, applications call the Active version over a REST API and get an answer in milliseconds. Every call is kept in run history, so "why did it do that?" finally has an answer.

The stored procedures that move data can stay exactly where they are.

Next steps

See how Condexa connects to SQL Server, read about rule changes that need developers, or weigh up build vs buy. Then see your own decision running in 30 minutes.