How ChatGPT Helped Me Write a Complex Stored Procedure in SQL Server

A couple of weeks ago, I was working on a project where I needed to write a mildly complex stored procedure (SPROC).

At a high level, I needed the stored procedure to do the following things:

• If missing, generate a "default" record in an existing Investigation table
• If missing, generate default records for a "report style" in two related tables
• If missing, generate records for an Investigation record based on the defaults for that Investigation's current "report style"

None of the individual parts of the stored procedure were particularly difficult to write, but writing the entire SPROC from scratch would have been tedious.  I decided to have ChatGPT do the light lifting–generating the bulk of the T-SQL–knowing that I would ultimately need to do the heavy lifting of making the thing actually work.

This mindset shift is key to making ChatGPT work for you instead of against you.

Instead of asking ChatGPT, "Can I haz the code?" (like the worst kind of programming forum user) and then being disappointed with the results, you need to tell ChatGPT exactly what you need, and then ask it to provide you with a first draft.

You will find ChatGPT much more satisfying to use if you treat it like an intern instead of like a mentor.

A Short Case Study

To show you what I mean, I'm going to walk you through the interactions I had with ChatGPT to generate this stored procedure.

I won't bore you with every detail, but I will post a few of my prompts verbatim so that you understand the level of specificity you need to provide to get the best results.

Step 1. Establishing Context

One of the breakthroughs of ChatGPT and other similar services is that they remember context from previous portions of a conversation.  This remembered context is one of the key differentiators between current AI models and searching the web using Google.  Take advantage of it!

Here's my first prompt and response:

[MIKE]: I need to create a rather involved stored procedure to run in SQL Server.  I'm going to start by providing you the CREATE TABLE scripts for the involved tables.

[ChatGPT]: Sure, I'd be happy to help! Please provide the CREATE TABLE scripts for the tables involved in your stored procedure.

Step 2. Provide CREATE TABLE Scripts

The more information you provide ChatGPT, the better responses it can generate.

The next step is to provide it with information about the tables that will be involved in the stored procedure.  Here are the steps I followed to generate my next set of prompts:

1. Go to SSMS
2. Right-click the table and choose "Script Table as" > "CREATE To" > "Clipboard"
3. In ChatGPT, type "Here's the first (next) table:"
4. Press Shift + Enter twice to add two lines without submitting the prompt
5. Press Ctrl + V to paste the CREATE TABLE script
6. Hit Enter to submit the prompt
7. Repeat steps 2-6 for all involved tables

This step is easy to do and results in massively better output from ChatGPT.

Step 3. Tell ChatGPT What You Want in Excruciating Detail

In the introduction of this article, I included a three-bullet "high-level overview" of what I needed this stored procedure to do.  Contrast that with the next prompt that I actually fed to ChatGPT (you don't need to read the whole thing):

[MIKE]: Now, write a stored procedure to do the following:
- Accept an InvestigationID as the lone input parameter and assign it to @InvestigationID
- Check to see if a psi.Investigation record exists where InvestigationID = 0
- If not, then create a new psi.Investigation record to act as a "default" investigation (we'll need to use IDENTITY_INSERT since InvestigationID is an autonumber column)
- Check to see if there are psi.PsiStatus records for every record in the psi.PsiSection table where InvestigationID = 0
- If not, create any missing psi.PsiStatus records where InvestigationID = 0; set the StatusCode to 'U' for any records that need to be added
- Get the luPsiRptStyleID from the Investigation record where psi.Investigation.InvestigationID = @InvestigationID and assign it to @luPsiRptStyleID
- Check to see if there are psi.PsiStyleStatus records for every record in the psi.PsiStatus table where InvestigationID = 0 and psi.PsiStyleStatus.luPsiRptStyleID = @luPsiRptStyleID
- If not, create any missing psi.PsiStyleStatus records where InvestigationID = 0; set the ShowOnReport field to -1 for any records that need to be added
- Check to see if there are psi.PsiStatus records for every record in the psi.PsiSection table where InvestigationID = @InvestigationID
- If not, create any missing psi.PsiStatus records where InvestigationID = @InvestigationID; set the StatusCode to match the StatusCode of the existing psi.PsiStatus records where InvestigationID = 0 for any records that need to be added
- Check to see if there are psi.PsiStyleStatus records for every record in the psi.PsiStatus table where InvestigationID = @InvestigationID and psi.PsiStyleStatus.luPsiRptStyleID = @luPsiRptStyleID
- If not, create any missing psi.PsiStyleStatus records where InvestigationID = @InvestigationID and psi.PsiStyleStatus.luPsiRptStyleID; set the ShowOnReport field to match the ShowOnReport field of the existing psi.PsiStyleStatus records where InvestigationID = 0 for any records that need to be added

I didn't expect you to read the entire block of text above.  All I wanted to illustrate was the level of detail you need to provide to ChatGPT.

Again, imagine that you are talking to the world's most resourceful and hard-working intern.  If you give them very specific, very explicit instructions they will research the problem tirelessly and give you ... something.  

In many cases, it will be exactly what you need.  In other cases, it will be close, but not quite perfect.  And in rare cases, it will be way off*.

^{* Most examples of poor ChatGPT responses you see on the web are more of a reflection of poor "prompt-crafting" than a problem with the model; even with AI, the old adage applies, "Garbage in, garbage out".}

Step 4. Refining the Results

Chances are that ChatGPT's first attempt at a stored procedure of this complexity will not even compile.  

Here is where AI is not yet at the point where it can replace a good programmer.

Copy the generated stored procedure, paste it into SSMS, and let it check for errors.  When I did this, the SPROC wouldn't compile because ChatGPT kept referring to a field that didn't actually exist on one of the tables.  The field was on a different table and it was needed to make the SPROC work, but it required adding a JOIN to the query to make the field accessible.

Here was my next prompt:

[MIKE]: Here's the current SPROC:

<stored procedure pasted into original prompt>

It doesn't compile yet because it keeps referring to an InvestigationID field on the PsiStyleStatus table.  That field does not exist.  Instead, the field is on the PsiStatus table to which the PsiStyleStatus table is joined (via the PsiStatusID fields). Can you rewrite the SPROC accounting for this issue?

ChatGPT fixed its mistake in much the same way an intern would have fixed their mistake given guidance from an experienced mentor.

A few additional iterations were needed to get the SPROC to compile and execute.

Step 5. Testing the Stored Procedure

This last step is critical.

Remember, it is easier to write code than it is to read it.

By definition, it will be more difficult to understand what ChatGPT-written code will do than if you wrote the code yourself.  After all, you know what you are thinking when you write code...at least you should...most of the time.

Since we can't fully trust ChatGPT's code (it's an intern, remember?).  And we can't fully trust ourselves to read and understand how ChatGPT's code works.  We need a way to verify the code works.  And for that, we need tests.

This does not have to be some fancy SQL Server unit testing framework.  

Instead, you can do some quick and dirty SQL Server testing using my B-BEAR framework:

• Begin transaction
• Before
• Execute
• After
• Rollback transaction

B-BEAR: Quick and Dirty SQL Server Testing

Begin, Before, Execute, After, Rollback. This technique makes testing your SQL Server statements a snap.

No Longer SetMike Wolfe

Final Thoughts

If you think of ChatGPT–and treat ChatGPT–like an intern instead of an all-powerful wizard, you will be much more satisfied with how it helps you develop software.

Just remember, YOU are the all-powerful wizard, not the AI.

...

At least for now.

...

Enjoy it while it lasts.

...

[ominous noises]

...