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.
