Modernising SQL Server Stored Procedures with AI: From T-SQL to a Tested C# Service Layer

Your database is a second application

We often see the same pattern in legacy .NET estates. The application code went through code review and source control from day one, but the database logic grew differently. A procedure was added to fix a support incident, a trigger for an integration, and business rules went wherever the data was, because that was the fastest place to put them.

Over a couple of decades the totals add up. Estates with several hundred procedures and very large volumes of T-SQL are common, and that logic often implements the most important rules the business has, such as pricing, customer credit limits, eligibility, and invoicing. It is also the least tested and least reviewed layer of the whole system, and the hardest to hire for.

Any serious modernisation programme has to deal with this layer. As our .NET modernisation playbook sets out, rebuilding the application while leaving the rules in T-SQL leaves the hardest dependency untouched.

Should business logic live in stored procedures at all?

Some of it should. The engineering case for moving procedural business rules out of T-SQL is strong, but it does not extend to emptying the database.

Why business rules are better in C#:

• Testability. A C# service gets unit tests, mocks, and CI. Testing a stored procedure means standing up a database with the right seeded state, which is why most procedure estates have effectively no tests.
• Version control and review. Application code goes through pull requests. In many organisations, procedure changes are still applied by hand and reconstructed later from backups.
• Talent and tooling. The pool of engineers fluent in large procedural T-SQL estates is shrinking, and most modern quality tooling (analysers, mutation testing, coding agents) works better against C#.

Why set-based work should stay:

• The database engine is the right tool for operations over large sets, such as bulk updates, aggregations, and reporting joins. Translating a set-based UPDATE over a million rows into row-by-row application code makes the system slower and the cloud bill bigger.
• Transactional integrity for multi-table batch operations is simpler and safer close to the data.

Each procedure needs its own decision, so classification comes before any translation.

How do you classify a large procedure estate?

Four categories cover almost everything, and each has a different destination:

The first category shapes the whole plan, so it needs a note. We recommend keeping pure data access in stored procedures on its merits, and we do not see it as a compromise. Parameterised procedures behind a repository layer keep query plans and set logic close to the engine. They also let the application identity hold EXECUTE permissions only, with no direct table access, so modernisation leaves this access pattern in place and targets the business logic that grew inside procedural T-SQL.

Inventories tend to turn up two things worth planning for. The first is dead weight: estates often carry procedures with no remaining call sites, and identifying them safely (including dynamic SQL call sites) removes work before it is estimated. The second is duplication with variations, where the same rule is implemented three ways in three procedures. That needs a business decision about which version is correct, rather than three separate translations.

How do agents inventory and map the estate?

Reading an estate of that size by hand is not economically viable, which is why this layer survives most modernisation attempts. It is also work that coding agents do well. The workflow follows the assess, map, extract-rules, transform, and validate stages of Anthropic’s code modernisation playbook.

The inventory pass produces three artefacts:

1. A procedure catalogue. Every procedure classified into the four categories above, with size, complexity signals (cursor use, dynamic SQL, nesting depth), and a plain-English summary of what it appears to do.
2. A dependency map. Which procedures call which, which tables each reads and writes, and which application call sites invoke each procedure. The map turns ‘we think this is safe to move’ into evidence, and it is how dead procedures are found.
3. A rules extract for the business-logic category. The policy each procedure implements, written in plain English for review. Finding out that a rule is obsolete or contested costs least before anyone rebuilds it.

Long procedures put the most pressure on model context. A procedure running to several thousand lines, with nested temp-table pipelines, needs the whole procedure, its dependencies, and the relevant schema in view at once. The context strategies in our scaling coding agents guide apply directly. We recommend giving the agent a scoped, read-only schema source rather than connecting it to the whole database, and our MCP patterns guide covers how to build one.

People review the catalogue with the business before anything is translated. Agents produce the inventory, but the decisions that follow from it are made by people.

How do you translate a procedure with behaviour parity?

Work through the estate one procedure at a time: pin its behaviour, translate it, prove parity, then cut over its call sites. We would not batch-translate a whole estate in one pass.

Consider a typical business-rule procedure:

CREATE PROCEDURE dbo.ApproveOrderCredit
    @CustomerId INT,
    @OrderTotal DECIMAL(18,2),
    @Approved BIT OUTPUT,
    @Reason NVARCHAR(200) OUTPUT
AS
BEGIN
    DECLARE @CreditLimit DECIMAL(18,2), @Outstanding DECIMAL(18,2);

    SELECT @CreditLimit = CreditLimit FROM dbo.Customers
    WHERE CustomerId = @CustomerId;

    SELECT @Outstanding = ISNULL(SUM(Total), 0) FROM dbo.Orders
    WHERE CustomerId = @CustomerId AND Status IN ('Open', 'Invoiced');

    IF @CreditLimit IS NULL
    BEGIN
        SET @Approved = 0;
        SET @Reason = 'No credit limit on file';
        RETURN;
    END

    IF @Outstanding + @OrderTotal > @CreditLimit
    BEGIN
        SET @Approved = 0;
        SET @Reason = 'Order would exceed credit limit';
        RETURN;
    END

    SET @Approved = 1;
    SET @Reason = NULL;
END

The C# translation makes the rule visible, injectable, and testable:

public class CreditApprovalService(IOrderRepository orders, ICustomerRepository customers)
{
    public async Task<CreditDecision> ApproveAsync(
        int customerId,
        decimal orderTotal,
        CancellationToken cancellationToken = default)
    {
        var creditLimit = await customers.GetCreditLimitAsync(customerId, cancellationToken);
        if (creditLimit is null)
            return CreditDecision.Declined("No credit limit on file");

        var outstanding = await orders.GetOutstandingTotalAsync(customerId, cancellationToken);
        if (outstanding + orderTotal > creditLimit.Value)
            return CreditDecision.Declined("Order would exceed credit limit");

        return CreditDecision.Approved();
    }
}

A clean compile says nothing about behaviour. Parity comes from a test that runs both implementations against the same seeded database and compares the outcomes, including the edge cases:

[Theory]
[InlineData(1001, 500.00)]   // well under limit
[InlineData(1001, 9999.99)]  // exactly at limit boundary
[InlineData(1002, 100.00)]   // customer with NULL credit limit
[InlineData(1003, 250.00)]   // customer with no orders (SUM returns NULL path)
public async Task NewService_Matches_LegacyProcedure(int customerId, double orderTotalValue)
{
    var orderTotal = (decimal)orderTotalValue; // attribute arguments cannot be decimal literals
    var legacy = await RunLegacyProcedureAsync(customerId, orderTotal);
    var modern = await creditService.ApproveAsync(customerId, orderTotal);

    modern.Approved.Should().Be(legacy.Approved);
    modern.Reason.Should().Be(legacy.Reason);
}

The NULL-handling cases are the most important. In the original, ISNULL(SUM(Total), 0) encodes a decision about customers with no orders, and a translation that misses it will approve or decline the wrong customers. Agents draft these tests well when the rules extract names the edge cases. Mutation testing, covered in our AI test quality guide, then checks that the tests would catch a divergence.

Cutover happens per call site. Application callers move to the service, the procedure is marked deprecated, and it is dropped after a monitored period. The dependency map from the inventory shows when a procedure has no remaining callers.

What changes when the target is Azure SQL?

If the platform move to Azure SQL is part of the programme, we recommend running Microsoft’s assessment rules against the estate at the start rather than the end. The findings affect the modernisation plan in three ways:

• Unsupported features (certain system stored procedures, cross-database references, server-scoped objects) have to be remediated anyway. If a procedure needs rework for compatibility, that is often a good time to classify and modernise it rather than patch it.
• SQL Agent jobs do not exist in Azure SQL Database. Job bodies are orchestration-category logic, and choosing their replacement (Azure Functions, container-based background services, or elastic jobs) is an application-architecture decision.
• Sequencing. Keeping the schema and the surviving set-based procedures stable while the application layer is modernised gives you a behavioural baseline. The platform migration then moves a known, tested estate, instead of rewriting and relocating it at the same time.

For the operational side of running a large SQL Server estate day to day, including AI-augmented query tuning and index management, see our automated DBA guide.

What to watch for

NULL semantics. T-SQL uses three-valued logic, which does not map directly to C#. Every WHERE clause comparison involving a nullable column, and every ISNULL and COALESCE, encodes behaviour that the translation has to preserve on purpose.

Implicit conversions and rounding. T-SQL converts between types silently and rounds DECIMAL arithmetic by its own rules. Financial calculations translated to C# decimal can differ in the last penny, and parity tests are there to catch that kind of difference.

Transaction boundaries. A procedure often assumes it runs inside the caller’s transaction, or it starts its own. The C# service needs a transaction design that someone has thought through, rather than one that happens by accident.

Collation and case sensitivity. String comparisons in the database follow collation rules, but C# comparisons do not follow the database collation. Sorting and equality can therefore diverge without any error.

Error handling. TRY/CATCH with RAISERROR conventions, return codes, and output parameters all need an explicit mapping to exceptions and result types. Callers may also depend on the old convention.

Temp table pipelines. Long procedures that stage data through temp tables are usually several logical steps fused together. Translate the steps rather than the table plumbing, and expect the rules extract to show that some steps exist only to work around a limitation that C# does not have.

Cursors. A cursor is sometimes a business rule, making per-row decisions that belong in C#. Sometimes it is a performance workaround, and then it should become a set-based statement that stays in the database rather than moving to C#. Each one needs classifying.

Next steps

We start with the inventory, because it turns an estate nobody can size into a measurable plan: how many procedures there are, in which categories, with which dependencies, and how much of it is dead. It is a short, bounded piece of work that ends with an artefact, and it gives every later decision an evidence base.

Related guides in this cluster:

• The .NET modernisation playbook for the strategic frame
• The WinForms to web guide if a desktop rebuild is part of the same programme
• The assessment framework for estate-level readiness scoring
• The Claude Code configuration guide for the tooling setup, and our Claude Code for .NET developers guide if your team is new to the tool
• The AI Velocity Report for our current first-party delivery metrics

If you want to size the problem, book a free consultation and we will start with the procedure inventory.