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

By:   |   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:

Users table

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
Copilot request

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.

hit Tab to accept the suggested code
hit Enter to see the function autocomplete itself
hit Enter to see the function autocomplete itself
hit Enter to see the function autocomplete itself
hit Enter to see the function autocomplete itself
hit Enter to see the function autocomplete itself
hit Enter to see the function autocomplete itself
hit Enter to see the function 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
Same Copilot request, but different way

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
2nd request outcome

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

--Create a SQL Server function that creates a new sql login
Request: 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
Request: Create a SQL Server function that creates a new sql login (2)

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
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
--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
--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
--Create a SQL Server Stored Procedure that performs insert in my dbo.users table

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
--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
totally made-up random Stored Procedure

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

execute

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!


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Alejandro Cobar 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


Article Last Updated: 2023-08-01

Comments For This Article




Friday, August 18, 2023 - 12:34:02 PM - Fermin Ornelas Back To Top (91499)
Good intro

Tuesday, August 1, 2023 - 10:52:38 AM - Sri Back To Top (91445)
Thanks for sharing.