Introduction

In this article, we will learn how to work with ChatGPT in Visual Studio. We will install and use an extension in a SQL Server Database Project.

Requirements

1. First, we need to have SQL Server installed.
2. Secondly, we need to have Adventureworks installed.
3. Thirdly, SSDT in Visual Studio.
4. Finally, a ChatGPT login registration

Getting started with ChatGPT in Visual Studio – Install Visual ChatGPT Studio

First, in Visual Studio go to Extensions>Manage Extensions. There are some ChatGPT extensions. In this example, we will use Visual ChatGPT Studio. Write ChatGPT and press the install button to install the Visual ChatGPT Studio.

Secondly, in Visual Studio go to Tools>Options. Look for the Visual ChatGPT Studio and copy and paste the OpenAI API key.

Here you have an explanation of the parameters:

• First, we have Frequency, which is used to avoid repeating phrases.
• Secondly, we have Max Tokens to control the maximum number of words accepted.
• Thirdly, the Model Language is used to select the language used. Currently, I only see TextDancinci003 as the only language available right now.
• Also, we have the OpenAI API key to connect to the REST API.
• In addition, the presence penalty penalizes generating text too close to the input.
• Stop Sequences includes a list of tokens that the model should avoid.
• Temperature is used to control the creative text. A higher temperature, a more creative text.
• The top P is to create more diverse text.
• Finally, Turbo chat behavior is used to control the level of engagement and creativity.

You can get the API key from the OpenAI website. Also, you need to be registered in ChatGPT and have a login and password.

Finally, press OK to save the secret key.

Open a SQL Server Database Project

First, in Visual Studio, select the Create a new project option.

Secondly, in the Create a new project window, select SQL Server Database Project.

Thirdly, write a Project name and press Create.

Also, in the menu select View>SQL Server Object Explorer.

In addition, in the SQL Server Object Explorer, press the Add SQL Server icon.

Finally, write the SQL Server Name. Select your authentication and connect to the Adventureworks2019 database and press Connect.

Add ChatGPT in Visual Studio

First, in the menu select View>Other Windows>Visual ChatGPT Studio.

Secondly, let’s play with ChatGPT. I will add a request:

Me: Please create the code to find a stored procedure that contains the word log in its name. Once you write the question press the send icon.

ChatGPT:

SELECT name
FROM sys.procedures
WHERE name LIKE '%log%';
GO

Thirdly, in the SQL Server Object Explorer, right-click the Adventureworks2019 database and select New query.

Also, copy and paste the code suggested by ChatGPT to the code pane. Select the code, right-click, and select the Execute option.

Finally, the query will show two stored procedures that contain the word log inside.

Generate stored procedures with ChatGPT in Visual Studio

First, right-click the Person.CountryRegion and select the Script As>CREATE to>New Query Window. To generate the create table code. Copy and paste the create table code.

Secondly, ask ChatGPT to generate the insert, update and delete stored procedures for the table:

Me: Please create insert update and delete stored procedures for this table:

CREATE TABLE [Person].[CountryRegion] (
[CountryRegionCode] NVARCHAR (3) NOT NULL,
[Name] [dbo].[Name] NOT NULL,
[ModifiedDate] DATETIME NOT NULL
);

ChatGPT:

-- Insert stored procedure
CREATE PROCEDURE [Person].[usp_InsertCountryRegion]
@CountryRegionCode NVARCHAR(3),
@Name [dbo].[Name],
@ModifiedDate DATETIME
AS
BEGIN
INSERT INTO [Person].[CountryRegion]
VALUES (@CountryRegionCode, @Name, @ModifiedDate)
END

-- Update stored procedure
CREATE PROCEDURE [Person].[usp_UpdateCountryRegion]
@CountryRegionCode NVARCHAR(3),
@Name [dbo].[Name],
@ModifiedDate DATETIME
AS
BEGIN
  UPDATE [Person].[CountryRegion]
  SET [Name] = @Name,
  [ModifiedDate] = @ModifiedDate
  WHERE [CountryRegionCode] = @CountryRegionCode
END

-- Delete stored procedure
CREATE PROCEDURE [Person].[usp_DeleteCountryRegion]
@CountryRegionCode NVARCHAR(3)
AS
BEGIN
  DELETE FROM [Person].[CountryRegion]
  WHERE [CountryRegionCode] = @CountryRegionCode
END

ChatGPT created 3 stored procedures

• First, usp_InsertCountryRegion to insert data.
• Secondly, usp_UpdateCountryRegion to update data.
• Finally, usp_DeleteCountryRegion is used to delete rows.

Working with ChatGPT in Visual Studio and stored procedures

First, to invoke the stored procedure, I will send the stored procedure and ask ChatGPT to insert data using the existing stored procedure.

Me: Please insert 1 row using this stored procedure

ChatGPT:

EXEC [Person].[usp_InsertCountryRegion] 'USA', 'United States', '2020-01-01'

If I try to run the code, I will get the following error message:

Msg 2601, Level 14, State 1, Procedure Person.usp_InsertCountryRegion, Line 81

Cannot insert duplicate key row in object 'Person.CountryRegion' with unique index 'AK_CountryRegion_Name'. The duplicate key value is (United States).

The statement has been terminated.

This is because the USA country already exists. Then the value is duplicated.

I will run the following code to get all the row values:

Select *
from
[Person].[CountryRegion]

Also, I will copy the values of the select and ask ChatGPT to insert data not included in the existing data.

Me: Please insert 5 rows using this stored procedure:

-- Insert stored procedure
CREATE PROCEDURE [Person].[usp_InsertCountryRegion]
@CountryRegionCode NVARCHAR(3),
@Name [dbo].[Name],
@ModifiedDate DATETIME
AS
BEGIN
  INSERT INTO [Person].[CountryRegion]
   VALUES (@CountryRegionCode, @Name, @ModifiedDate)
END

Also, make sure that the values inserted are not in this list:

CountryRegionCode Name                 ModifiedDate
AD                Andorra              2008-04-30 00:00:00.000
AE                United Arab Emirates 2008-04-30 00:00:00.000
AF                Afghanistan          2008-04-30 00:00:00.000
AG                Antigua and Barbuda  2008-04-30 00:00:00.000
AI                Anguilla             2008-04-30 00:00:00.000
AL                Albania              2008-04-30 00:00:00.000
AM                Armenia              2008-04-30 00:00:00.000
AN                Netherlands Antilles 2008-04-30 00:00:00.000
.....

ChatGPT:

---------------------------

Visual chatGPT Studio

---------------------------

Error at completions (https://api.openai.com/v1/completions) with HTTP status code: BadRequest. Content: {

"error": {

"message": "This model's maximum context length is 4097 tokens, however you requested 7014 tokens (4966 in your prompt; 2048 for the completion). Please reduce your prompt; or completion length.",

"type": "invalid_request_error",

"param": null,

"code": null

}

}

ChatGPT has a limitation of 4097 tokens. If your request is longer, you may have problems. To fix this problem, you may want to use the ChatGPT REST API paid version or send a smaller dataset. We will try to send only the CountryRegionCode:

Me: Please insert 5 rows using this stored procedure:

-- Insert stored procedure
CREATE PROCEDURE [Person].[usp_InsertCountryRegion]
@CountryRegionCode NVARCHAR(3),
@Name [dbo].[Name],
@ModifiedDate DATETIME
AS
BEGIN
  INSERT INTO [Person].[CountryRegion]
  VALUES (@CountryRegionCode, @Name, @ModifiedDate)
END

Check values that are not in the table

Also, make sure that the values inserted are not in this list:

CountryRegionCode
AD
AE
AF
AG
AI
AL
AM
AN
AO
AQ
AR
AS
AT
AU
AW
AZ
BA
BB
GD
......

ChatGPT:

EXEC [Person].[usp_InsertCountryRegion] 'XX', 'Test', GETDATE();
EXEC [Person].[usp_InsertCountryRegion] 'YY', 'Test', GETDATE();
EXEC [Person].[usp_InsertCountryRegion] 'ZZ', 'Test', GETDATE();
EXEC [Person].[usp_InsertCountryRegion] 'AA', 'Test', GETDATE();
EXEC [Person].[usp_InsertCountryRegion] 'AB', 'Test', GETDATE();

Unfortunately, this code will not work. This code is using the GETDATE(); function which is not allowed in a stored procedure.

The error message generated is the following:

Msg 102, Level 15, State 1, Line 76

Incorrect syntax near ')'.

In this example, we will try a different way to insert data.

Me: Please insert 5 rows using this stored procedure:

-- Insert stored procedure

CREATE PROCEDURE [Person].[usp_InsertCountryRegion]
@CountryRegionCode NVARCHAR(3),
@Name [dbo].[Name],
@ModifiedDate DATETIME
AS
BEGIN
  INSERT INTO [Person].[CountryRegion]
  VALUES (@CountryRegionCode, @Name, @ModifiedDate)
END

Use countries from the game of thrones. Do not use the getdate function for the modified date. Use random dates with quotes.

ChatGPT:

EXEC [Person].[usp_InsertCountryRegion] 'WES', 'Westeros', '2020-01-01'
EXEC [Person].[usp_InsertCountryRegion] 'ESS', 'Essos', '2019-02-15'
EXEC [Person].[usp_InsertCountryRegion] 'DOR', 'Dorne', '2018-03-20'
EXEC [Person].[usp_InsertCountryRegion] 'IRL', 'Iron Islands', '2017-04-25'
EXEC [Person].[usp_InsertCountryRegion] 'VAL', 'Valyria', '2016-05-30'

This time, ChatGPT worked fine. I will run the code to verify that the data was successfully inserted:

SELECT TOP (1000) [CountryRegionCode]
,[Name]
,[ModifiedDate]
FROM [AdventureWorks2019].[Person].[CountryRegion]
where [name] in ('Westeros','Essos','Dorne','Iron Islands','Valyria')

Finally, if everything is fine, you will have the following values:

Some ChatGPT in Visual Studio options

First,  select a portion of the code and right-click, you will see some options to Add Tests, Complete code, Find Bugs, Optimize code, Explain, Add Comments, and other options.

These options are not smart enough yet.

-- Unit Test 1
-- Test that the procedure returns the correct number of records
DECLARE @RecordCount INT
EXEC [dbo].[uspGetBillOfMaterials] @StartProductID = 709, @CheckDate = '2019-01-01', @RecordCount = @RecordCount OUTPUT
IF @RecordCount = 8
 PRINT 'Test 1 Passed'
ELSE
 PRINT 'Test 1 Failed'
-- Unit Test 2
-- Test that the procedure returns the correct component description
DECLARE @ComponentDesc NVARCHAR(50)
EXEC [dbo].[uspGetBillOfMaterials] @StartProductID = 709, @CheckDate = '2019-01-01'
, @ComponentDesc = @ComponentDesc OUTPUT
IF @ComponentDesc = 'Chainring Bolts'
  PRINT 'Test 2 Passed'
ELSE
 PRINT 'Test 2 Failed'

If you try to run the code, you will notice that it does not work and you will require to modify it. These options have some mistakes and need to be improved for T-SQL. The explain and add comments options may be useful. However, the options to optimize, and find bugs are not so useful.

Conclusion

In this article, we learned how to work with ChatGPT in Visual Studio. Also, we learned how to install an extension and we worked with it. ChatGPT still needs to improve. To conclude, we can say that ChatGPT has a lot of basic mistakes yet, but I think it can be improved over the years and be a nice assistant for programming.

If you learn its limitations and strengths, it can be useful in several tasks.