How ChatGPT Helped Me Refactor a Messy SQL Procedure Using Strategy Pattern Principles

I recently had to work with a long, convoluted SQL Server stored procedure that had grown over time into an unmaintainable block of logic. It handled order synchronization across different warehouse systems — but was packed with nested IF...ELSE conditions, hardcoded vendor names, and copy-pasted logic for each case.

It was working, sure. But any small change felt like defusing a bomb.
Touch it here, and it breaks something there. Every Friday deploy felt like Russian roulette.

The Problem

This SP looked something like this (simplified):

IF @Vendor = 'SCE'
BEGIN
    -- logic for SCE
END
ELSE IF @Vendor = 'WMS1'
BEGIN
    -- same logic, slight tweak
END
ELSE IF @Vendor = 'WMS2'
BEGIN
    -- again, 90% same
END
-- ... more vendors, more blocks ...

Imagine 800+ lines of almost-repeating logic.

We had a new vendor coming in. And I thought: "I cannot go on duplicating these blocks. This needs cleanup before something explodes."

I knew what to do — but needed clarity.

I’ve worked with design patterns before, mostly in C# and JS. The Strategy Pattern came to mind. I knew I needed to isolate vendor-specific logic into modular, manageable chunks — just like we'd do with classes.

I was assessing the most efficient way to implement this in SQL Server.

So I turned to ChatGPT — not to tell me what I didn’t know, but to accelerate my decision-making.

The ChatGPT Journey Begins

🔸 First, I framed the problem clearly:

Prompt:

“I have a stored procedure with multiple IF-ELSE conditions for different vendor types. Each block has mostly similar logic, with minor changes. I want to refactor this using a strategy-pattern-like approach in SQL Server. How should I go about this?”

ChatGPT responded:

"In SQL Server, you can't use classes or interfaces like in OOP, but you can mimic the Strategy Pattern using:
Vendor-specific procedures or functions
A dispatcher or controller procedure that calls them dynamically
A metadata-driven config approach for flexibility"

That clicked. Exactly what I had in mind.

🔸 Next, I pushed for structure:

Prompt:

“Can you help me design a SQL-based version of strategy pattern using stored procedures? I want a controller procedure to call vendor-specific procedures. Also show how I can pass parameters dynamically.”

ChatGPT output:

Certainly! While SQL Server doesn't support object-oriented features like interfaces or inheritance directly, you can emulate the Strategy Pattern using stored procedures and dynamic SQL.
Here’s how you can implement it:
1. Create a Config Table to Map Vendors to Procedures
This decouples logic from the controller:

CREATE TABLE VendorProcedureMapping (
    VendorCode NVARCHAR(50) PRIMARY KEY,
    ProcedureName NVARCHAR(100) NOT NULL
)

Sample Data:

INSERT INTO VendorProcedureMapping (VendorCode, ProcedureName)
VALUES 
    ('SCE', 'Sync_SCE_Orders'),
    ('WMS1', 'Sync_WMS1_Orders'),
    ('WMS2', 'Sync_WMS2_Orders')

2. Create Vendor-Specific Stored Procedures
Each vendor's procedure should follow a consistent signature.

CREATE PROCEDURE Sync_SCE_Orders
    @OrderID INT,
    @SyncMode NVARCHAR(20)
AS
BEGIN
    -- Vendor-specific logic
    PRINT 'Syncing SCE order'
END

Repeat similarly for other vendors.
3. Create a Controller Procedure to Dynamically Dispatch

CREATE PROCEDURE Sync_Controller
    @VendorCode NVARCHAR(50),
    @OrderID INT,
    @SyncMode NVARCHAR(20)
AS
BEGIN
    DECLARE @ProcName NVARCHAR(100)
    DECLARE @SQL NVARCHAR(MAX)

    -- Fetch the procedure name from mapping table
    SELECT @ProcName = ProcedureName
    FROM VendorProcedureMapping
    WHERE VendorCode = @VendorCode

    IF @ProcName IS NULL
    BEGIN
        RAISERROR('Unknown Vendor Code', 16, 1)
        RETURN
    END

    -- Construct dynamic SQL to call the vendor-specific procedure
    SET @SQL = 'EXEC ' + QUOTENAME(@ProcName) + 
               ' @OrderID = @OrderID, @SyncMode = @SyncMode'

    EXEC sp_executesql 
        @SQL, 
        N'@OrderID INT, @SyncMode NVARCHAR(20)', 
        @OrderID = @OrderID, 
        @SyncMode = @SyncMode
END

🔸 I refined it further:

Prompt:

"This design looks solid. I’ve created the controller and vendor-specific SPs and am routing calls using a config table.
Now help me make this production-ready:
I want to add logging at both controller and vendor SP levels
I also want to gracefully handle errors and capture failed vendor SP calls with vendor code and error message
Show me how to structure this robustly in SQL Server."

Chatgpt Response:

Absolutely, here’s how you can make the pattern production-grade:
1. Add logging table:

CREATE TABLE Sync_Log (
    LogID INT IDENTITY PRIMARY KEY,
    VendorCode NVARCHAR(50),
    ProcedureName NVARCHAR(100),
    Status NVARCHAR(20),
    ErrorMessage NVARCHAR(MAX),
    LoggedAt DATETIME DEFAULT GETDATE()
)

2. Update controller to include TRY-CATCH logging:

BEGIN TRY
    SET @SQL = N'EXEC ' + QUOTENAME(@ProcName) +
               N' @OrderID = @OrderID, @SyncMode = @SyncMode';

    EXEC sp_executesql 
        @SQL,
        N'@OrderID INT, @SyncMode NVARCHAR(20)',
        @OrderID = @OrderID, 
        @SyncMode = @SyncMode;

    INSERT INTO Sync_Log (VendorCode, ProcedureName, Status)
    VALUES (@VendorCode, @ProcName, 'Success')
END TRY
BEGIN CATCH
    INSERT INTO Sync_Log (VendorCode, ProcedureName, Status, ErrorMessage)
    VALUES (
        @VendorCode,
        @ProcName,
        'Failure',
        ERROR_MESSAGE()
    )
END CATCH

The New Design (Simplified)

We now have:

VendorConfig table:

Sync_Controller stored procedure:

DECLARE @ProcName NVARCHAR(100)

SELECT @ProcName = ProcedureName
FROM VendorConfig
WHERE VendorCode = @Vendor

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'EXEC ' + QUOTENAME(@ProcName) + ' @OrderID = ' + CAST(@OrderID AS NVARCHAR)

EXEC sp_executesql @SQL

Each vendor now has its own isolated procedure.
Adding a new one? Just plug it into the config table and write its proc.

The Second Challenge — Pre-deployment Dry Run

Right before deployment, I faced a genuine challenge.

“How do I ensure behavior hasn't changed?”

Earlier, we had a single SP. Now we had multiple. I needed to simulate a full sync run and compare outputs for multiple vendors.

I didn’t want to write separate SQL tests for each.

So, again — ChatGPT.

🔸 Prompt:

_“I have two stored procedures: the old monolithic one and the new refactored controller-based one. For a list of test OrderIDs and vendors, I want to:Run both proceduresCapture their output in tablesCompare differences (if any)”_

ChatGPT:

• Helped design a temp-table-based logging mechanism
• Showed how to wrap both procs in wrappers that insert results into tables
• Suggested a FULL OUTER JOIN diff-check across columns

We caught a couple of missing default values in one vendor's new procedure. Fixing those early saved us a weekend rollback.

What I Learned

• I knew what I wanted to do (use Strategy pattern ideas).
• ChatGPT didn’t “teach” me the concept — it helped me clarify, validate, and speed up implementation.
• It acted like a pair programmer, not a magician.

And that’s how it should be used.

Final Thought

This whole refactoring — from messy SP to strategy-driven design — took less time than writing this blog.
Not because I’m a SQL ninja, but because the right tool (ChatGPT) helped me think faster.

The win isn’t the tool.
It’s the clarity and momentum it provides.

💬 Let’s Connect
Enjoyed this article or have a perspective to share? Let’s connect on LinkedIn.