GitHub Copilot to Auto Generate Code for SQL Server Stored Procedures and Functions - Part 2

By: Alejandro Cobar   |   Updated: 2023-08-01   |   Comments (2)   |   Related: 1 | 2 | 3 | More > Artificial Intelligence

Problem

In part 1 of this series, we went through the basic account setup to use the Copilot extension in Azure Data Studio. We also checked a few basic examples of code suggestions based on declarative comments and on-the-fly while typing our T-SQL instructions.

Let's continue exploring what shortcuts Copilot brings to the table to see if we are still in the "productivity boost lane."

Solution

In this article, we will check what Copilot offers regarding "assistance to the database developer" for development around user-defined functions and stored procedures.

Initial Considerations

• This article will assume that your environment is set up with everything needed to follow along. If not, please refer to my previous article.
• The content will be demonstrated using a Windows environment, but it is assumed you can follow along using a Linux or MacOS environment.
• A fundamental understanding of stored procedures and functions will be assumed.

User-Defined Functions

Let's assume that all we have right now is the users table from the previous article:

Let's ask Copilot, in the form of declarative comments, to create a function for us that returns the email of the user based on its username. It is important to mention that you can ask for things in many different ways, but Copilot may or may not give you what you need.

Here's the comment that I will try:

--Create a SQL Server function that returns the email of a user based on the username
--Name it in camel case

When you hit "Enter," you will see the suggested code. You will also notice that it is not the whole deal; it is just the header of the function.

Let's hit "Tab" to accept the suggested code, and if you continue to hit "Enter," you will see how the function will autocomplete itself.

Sorry for bombarding you with screenshots. I wanted you to see exactly how I got to the result each step of the way. If you take a quick look at the generated code, you will notice that the logic needs some polish to be usable. Let's ask for the same function but in a slightly different way:

--Create a SQL Server function that returns the email of a user based on the username
--Name it in camel case
--Present the complete tsql code in 1 line

This is what I get this time. As you can see, just by adding that third line in the comment section, I got an output that looks cleaner and doesn't require any tweaking to become usable. This proves that slightly changing how you ask for things can vary the output you will get (for better or worse).

If I ask for the function to be used in a query, this is what I get:

--Use the function in a query

Let's try a couple of random things to see what we get:

--Create a SQL Server function that creates a new sql login

This is an interesting one: you clearly can't execute a stored procedure within a function, but Copilot suggests the code anyway.

Let's try this in a slightly different way to see what we get:

--Create a SQL Server function that creates a new sql login
--Do not execute the SP, just return the generated query

Now that looks better!

Stored Procedures

After playing around with the code generation for functions, I guess the story won't differ much for stored procedures. Regardless, let's see what Copilot can do for us.

For the first attempt, let me ask for a stored procedure that can give me the list of logins with sysadmin privilege:

--Create a SQL Server Stored Procedure that returns all sql logins with sysadmin privilege

Useful stuff with minimal effort. We're on the right track!

Now, using my existing users table, let's see if Copilot can create an SP to insert data.

--Create a SQL Server Stored Procedure that inserts data in my users table

For some reason, it didn't consider the definition of my existing user table. Let's see if I can change that:

--Create a SQL Server Stored Procedure that inserts data in my existing users table

Not even close. Let me try one final time:

--Create a SQL Server Stored Procedure that performs insert in my dbo.users table
--Use my existing table definition

Not quite, but a whole lot closer. Still, I couldn't get the full, accurate version I hoped for.

I'll now try to ask for a stored procedure to create, update, and delete a SQL login (more on the DBA side of things, but still an important task):

--Create a SQL Server Stored Procedure that lets me create, update and delete a sql login

It didn't deliver flawless T-SQL code, but it saved us a good chunk of time for sure.

Let me now try to ask for a totally made-up random stored procedure that sounds like this:

--Create a SQL Server Stored Procedure that does the following:
--Receive a parameter called @number
--Store in a table variable the factorial of @number
--Return the calculated value multiplied by 10

Let me execute it a couple of times to make sure it works as expected:

It actually worked as expected. That's pretty cool!

With that last example, I'm wrapping up this article. I hope you can take away a thing or two.

Next Steps

• As we have seen so far throughout the series, sometimes you will get a perfectly crafted response, sometimes you will get a somewhat good response that can work with some tweaks, and sometimes you can get a response that just won't work at all.
• You can affect your output by being very specific with your request or very vague/ambiguous.
• I strongly recommend using this tool for testing purposes only, not production environments. If you write proprietary code that gets sucked into the data repository that Copilot has at its disposal, you might get into trouble.
• So far, we've seen that Copilot can significantly help you reduce development time. In its current state, my opinion is that it does not threaten a database developer's job (just in case you are worried about that). I will say that the database developer who takes the most advantage of tools like this can easily overshadow the one who rests on their laurels.
• I will continue exploring the tool in the next chapters of this series, so stay tuned!

About the author

Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips