Category: Blog

Can an AI help me with some database API work? Let’s see.

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

The Scenario

One of the things that helped me support a number of development teams with their database work was having code generate code. I had a piece of code that could read the system tables in SQL Server and produce insert, update, and delete procedures.

I wondered if an AI could do this easily.

Using Copilot

I decided to try this first in the Edge Copilot window. I wrote this prompt:Create the T-sQL code for insert, update, and delete procedures for these tables:

I then pasted in the code for 5 tables from Northwind, such as this one:

CREATE TABLE "Employees" ( "EmployeeID" "int" IDENTITY (1, 1) NOT NULL , "LastName" nvarchar (20) NOT NULL , "FirstName" nvarchar (10) NOT NULL , "Title" nvarchar (30) NULL , "TitleOfCourtesy" nvarchar (25) NULL , "BirthDate" "datetime" NULL , "HireDate" "datetime" NULL , "Address" nvarchar (60) NULL , "City" nvarchar (15) NULL , "Region" nvarchar (15) NULL , "PostalCode" nvarchar (10) NULL , "Country" nvarchar (15) NULL , "HomePhone" nvarchar (24) NULL , "Extension" nvarchar (4) NULL , "Photo" "image" NULL , "Notes" "ntext" NULL , "ReportsTo" "int" NULL , "PhotoPath" nvarchar (255) NULL , CONSTRAINT "PK_Employees" PRIMARY KEY CLUSTERED ( "EmployeeID" ), CONSTRAINT "FK_Employees_Employees" FOREIGN KEY ( "ReportsTo" ) REFERENCES "dbo"."Employees" ( "EmployeeID" ), CONSTRAINT "CK_Birthdate" CHECK (BirthDate < getdate()) ) GO CREATE INDEX "LastName" ON "dbo"."Employees"("LastName") GO CREATE INDEX "PostalCode" ON "dbo"."Employees"("PostalCode") GO

I got this result:

-- Insert Procedure
CREATE PROCEDURE InsertEmployee
     @LastName nvarchar(20),
     @FirstName nvarchar(10),
     @Title nvarchar(30),
     @TitleOfCourtesy nvarchar(25),
     @BirthDate datetime,
     @HireDate datetime,
     @Address nvarchar(60),
     @City nvarchar(15),
     @Region nvarchar(15),
     @PostalCode nvarchar(10),
     @Country nvarchar(15),
     @HomePhone nvarchar(24),
     @Extension nvarchar(4),
     @Photo image,
     @Notes ntext,
     @ReportsTo int,
     @PhotoPath nvarchar(255)
AS
BEGIN
     INSERT INTO Employees (LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath)
     VALUES (@LastName, @FirstName, @Title, @TitleOfCourtesy, @BirthDate, @HireDate, @Address, @City, @Region, @PostalCode, @Country, @HomePhone, @Extension, @Photo, @Notes, @ReportsTo, @PhotoPath)
END
GO

-- Update Procedure
CREATE PROCEDURE UpdateEmployee
     @EmployeeID int,
     @LastName nvarchar(20),
     @FirstName nvarchar(10),
     @Title nvarchar(30),
     @TitleOfCourtesy nvarchar(25),
     @BirthDate datetime,
     @HireDate datetime,
     @Address nvarchar(60),
     @City nvarchar(15),
     @Region nvarchar(15),
     @PostalCode nvarchar(10),
     @Country nvarchar(15),
     @HomePhone nvarchar(24),
     @Extension nvarchar(4),
     @Photo image,
     @Notes ntext,
     @ReportsTo int,
     @PhotoPath nvarchar(255)
AS
BEGIN
     UPDATE Employees
     SET LastName = @LastName, FirstName = @FirstName, Title = @Title, TitleOfCourtesy = @TitleOfCourtesy, BirthDate = @BirthDate, HireDate = @HireDate, Address = @Address, City = @City, Region = @Region, PostalCode = @PostalCode, Country = @Country, HomePhone = @HomePhone, Extension = @Extension, Photo = @Photo, Notes = @Notes, ReportsTo = @ReportsTo, PhotoPath = @PhotoPath
     WHERE EmployeeID = @EmployeeID
END
GO




-- Delete Procedure
CREATE PROCEDURE DeleteEmployee
     @EmployeeID int
AS
BEGIN
     DELETE FROM Employees WHERE EmployeeID = @EmployeeID
END
GO

Here’s an image of what I saw:

You might ask if this is better than what I’d write. I wondered myself, so I asked it to change the update procedure slightly. Here’s the prompt and result:

-- Update Procedure
CREATE PROCEDURE UpdateEmployee
     @EmployeeID int,
     @LastName nvarchar(20) = NULL,
     @FirstName nvarchar(10) = NULL,
     @Title nvarchar(30) = NULL,
     @TitleOfCourtesy nvarchar(25) = NULL,
     @BirthDate datetime = NULL,
     @HireDate datetime = NULL,
     @Address nvarchar(60) = NULL,
     @City nvarchar(15) = NULL,
     @Region nvarchar(15) = NULL,
     @PostalCode nvarchar(10) = NULL,
     @Country nvarchar(15) = NULL,
     @HomePhone nvarchar(24) = NULL,
     @Extension nvarchar(4) = NULL,
     @Photo image = NULL,
     @Notes ntext = NULL,
     @ReportsTo int = NULL,
     @PhotoPath nvarchar(255) = NULL
AS
BEGIN
     UPDATE Employees
     SET LastName = COALESCE(@LastName, LastName),
         FirstName = COALESCE(@FirstName, FirstName),
         Title = COALESCE(@Title, Title),
         TitleOfCourtesy = COALESCE(@TitleOfCourtesy, TitleOfCourtesy),
         BirthDate = COALESCE(@BirthDate, BirthDate),
         HireDate = COALESCE(@HireDate, HireDate),
         Address = COALESCE(@Address, Address),
         City = COALESCE(@City, City),
         Region = COALESCE(@Region, Region),
         PostalCode = COALESCE(@PostalCode, PostalCode),
         Country = COALESCE(@Country, Country),
         HomePhone = COALESCE(@HomePhone, HomePhone),
         Extension = COALESCE(@Extension, Extension),
         Photo = COALESCE(@Photo, Photo),
         Notes = COALESCE(@Notes, Notes),
         ReportsTo = COALESCE(@ReportsTo, ReportsTo),
         PhotoPath = COALESCE(@PhotoPath, PhotoPath)
     WHERE EmployeeID = @EmployeeID
END
GO

That’s interesting. If I pass in nulls for everything, I get an update in place. Not quite what I’d do, as I’d check if the param was null and not include it in an update, which could reduce logging, but this is likely just as efficient.

Easy code to write for sure, and I could likely put together a bit of code to do this for me, but it’s nice that I can quickly ask an AI to do this. I think this is the type of thing that might be easier for developers to do, and maybe more likely. They get to play with AI and feel more productive. They can even alter a table and “re-ask” for new procedures where they can interact with the table.

What’s more, the INSERT procedures have the explicit column lists, so this means I could rev the underlying table without breaking this code. It’s a good coding practice, but far too many people would just embed a this in their code:

insert Employee values ‘Jones’, ‘Steve’, ‘Editor’, …

A quick thing, but Copilot seemed to work well here.