Using a GenAI to Help Brent – SQLServerCentral

Brent had a query exercise recently about train stations moving in some order and having overrides to this order in emergencies. I suppose he’s been traveling a lot lately and ran into an issue.

In any case, I decided to see if Claude could solve his challenge.

This is part of a series of experiments with AI systems.

The Prompt

I put this into Claude, mostly copy/paste from Brent’s challenge.

I have a sql table of stations in a physical order. Here is the table and data for stations DROP TABLE IF EXISTS dbo.Stations; CREATE TABLE dbo.Stations     (StationId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,      StationName VARCHAR(50),      StationPhysicalOrder INT); INSERT INTO dbo.Stations (StationName, StationPhysicalOrder)     SELECT CHAR(64 + n), n     FROM (VALUES         (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),         (12),(13),(14),(15),(16),(17),(18),(19)     ) AS Numbers(n);

I also have an override table, which contains its own data for emergencies. Here is this code: Transact-SQL 1 2 3 4 5 6 7 8 DROP TABLE IF EXISTS dbo.StationRoutingOverride; CREATE TABLE dbo.StationRoutingOverride     (StationRoutingOverrideId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,      StationFromName VARCHAR(50),      StationToName VARCHAR(50)); INSERT INTO dbo.StationRoutingOverride (StationFromName, StationToName)     VALUES (‘E’, ‘S’), (‘B’, ‘I’), (‘I’, ‘D’);

write a query to return a result set that: * Starts with the station with the lowest StationPhysicalOrder * For each row, if there’s a row in StationRoutingOverride to dictate the next step, jump to that station * Otherwise, go to the station with the next StationPhysicalOrder

The Answer

Here’s the code:

WITH StationRouting AS (
     -- Base case: Start with the station having the lowest StationPhysicalOrder
     SELECT 
         s.StationId,
         s.StationName,
         s.StationPhysicalOrder,
         1 as StepNumber
     FROM dbo.Stations s
     WHERE s.StationPhysicalOrder = (SELECT MIN(StationPhysicalOrder) FROM dbo.Stations)
     
     UNION ALL
     
     -- Recursive case: Find the next station
     SELECT 
         next_station.StationId,
         next_station.StationName,
         next_station.StationPhysicalOrder,
         sr.StepNumber + 1
     FROM StationRouting sr
     CROSS APPLY (
         SELECT TOP 1
             s.StationId,
             s.StationName,
             s.StationPhysicalOrder
         FROM dbo.Stations s
         WHERE s.StationName = COALESCE(
             -- First priority: Check if there's an override for current station
             (SELECT sro.StationToName 
              FROM dbo.StationRoutingOverride sro 
              WHERE sro.StationFromName = sr.StationName),
             -- Second priority: Next station by physical order
             (SELECT TOP 1 s2.StationName 
              FROM dbo.Stations s2 
              WHERE s2.StationPhysicalOrder > sr.StationPhysicalOrder 
              ORDER BY s2.StationPhysicalOrder)
         )
     ) next_station
     WHERE next_station.StationName IS NOT NULL
)
SELECT 
     StepNumber,
     StationId,
     StationName,
     StationPhysicalOrder
FROM StationRouting
ORDER BY StepNumber;

That doesn’t work, so I added to the prompt:

I got a similar error again with the group by in the CTE and pasted that in. The query got rewritten (Again) and this time it ran with this result, which matches Brent’s answer.

GenAIs make mistakes, but they can also fix things. I wondered how well this would work, but it worked well. I merely told the model it had made an error and it tried again.

Imagine if the AI could connect to a db, run its code, and adjust from the error messages itself? The higher end coding tools are supposed to do this.