ChatGPT in Visual Studio Code

In this article, we will play with a ChatGPT extension for Visual Studio Code to create code. We will play with the T-SQL code and show what this extension can do. If you are not familiar with ChatGPT, you can check our previous articles.

Requirements to install the ChatGPT extension in Visual Studio Code

In order to start, you need to have Visual Studio Code installed. This is a light and great code editor. It works in Microsoft, Mac, and Linux. You can write several languages like Python, SQL, C++, and Java. Visual Studio Code is integrated with Git. Please install it and follow these steps to configure the ChatGPT extension.

First, we will install ChatGPT: write and improve code using AI. Press the Extension icon using the Extension icon at the left. There are several extensions. We may watch other extensions in the next articles.

Secondly, search for the ChatGPT: write and improve code using AI. Select it and press Install.

Thirdly, go to the menu and select File>Preferences>Settings. We need to connect our Extension to the ChatGPT REST API.

Also, look for ChatGPT and click on the URL. This URL will take you to the ChatGPT REST API. Basically, we will connect to the API by using a secret key provided in the link.

In addition, press the Open button to open the page.

Press the Create new secret key button to create a new key and copy it.

Also, copy the key in the Chatgpt Api Key text box in Visual Studio Code.

Finally, you can customize the orders of the Extension.

Installing the SQL Server (mssql) extension

In this section, we will install the SQL Server extension in Visual Studio Code.

First, go to Extensions.

Secondly, select the SQL Server (mssql) created by Microsoft and press the Install button.

Thirdly, click on the SQL Server icon after the installation.

Press the + icon to add a new connection.

Also, write the SQL Server instance name instance name.

In addition, write the database name. In this example Adventureworks2019.

Finally, select the authentication type. Integrated is the Windows authentication. SQL login is to log in with logins created in SQL Server. Azure AD is for Azure databases.

Working with ChatGPT in Visual Studio Code – Explain the code

First, to the connection created and go to Programmability>Stored Procedures>dbo.uspLogError.

Secondly, select all the code and right-click and select ChatGPT: Explain selection option.

Thirdly ChatGPT will explain to you all the code and the parameters and what the store procedure does.

Next, I will select use this line of code and right-click and select the ChatGPT: Explain selection option.

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'uspLogError'
GO

Finally, you will get the following explanation.

I like ChatGPT helping me to understand the code let’s try other features.

Working with ChatGPT in Visual Studio Code – Refactor and Find Problems

There is an option to refactor the code. You can improve the design and quality of the code using the Refactor option. To do that, you need to follow these steps:

First, select all the code and right-click and select the ChatGPT: Refactor selection

Secondly, check the refactored code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- uspLogError logs error information in the ErrorLog table about the 
-- error that caused execution to jump to the CATCH block of a 
-- TRY...CATCH construct. This should be executed from within the scope 
-- of a CATCH block otherwise, it will return without inserting error 
-- information. 
CREATE PROCEDURE [dbo].[uspLogError] 
 @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted by uspLogError in the ErrorLog table. 
AS 
BEGIN
 SET NOCOUNT ON;
 -- Output parameter value of 0 indicates that error information was not logged. 
 SET @ErrorLogID = 0;
 BEGIN TRY
 -- Return if there is no error information to log. 
 IF ERROR_NUMBER() IS NULL RETURN;
 -- Return if inside an uncommittable transaction. Data insertion/modification is not allowed when a transaction is in an uncommittable state. 
 IF XACT_STATE() = -1 BEGIN PRINT 'Cannot log error since the current transaction is in an uncommittable state. Rollback the transaction before executing uspLogError in order to successfully log error information.'; RETURN; END
 INSERT [dbo].[ErrorLog] ([UserName], [ErrorNumber], [ErrorSeverity], [ErrorState], [ErrorProcedure], [ErrorLine], [ErrorMessage]) VALUES (CONVERT(sysname, CURRENT_USER), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE());
 -- Pass back the ErrorLogID of the row inserted. 
 SET @ErrorLogID = @@IDENTITY;
 END TRY
 BEGIN CATCH PRINT 'An error occurred in stored procedure uspLogError: '; EXECUTE [dbo].[uspPrintError]; RETURN -1; END CATCH; 
END; 
GO

Also, we will check if ChatGPT can find some problems in the code. Right-click the code and select ChatGPT: Find problems.

Finally, you will be able to see the ChatGTP output:

I will remove a parenthesis to check if ChatGPT fixes the problem:

Original code with the section modified:

INSERT [dbo].[ErrorLog]
(
[UserName],
[ErrorNumber],

And this is the segment of the code modified without parenthesis:

INSERT [dbo].[ErrorLog]
[UserName],
[ErrorNumber],

I will try to find problems again:

And yes, it provides a good explanation and also shows the correct code. What I like is that if you click on the code, it is copied.

Working with ChatGPT in Visual Studio Code – Optimize code and write documentation

In order to optimize the code, we will follow these steps:

First, select the code right click and select the ChatGTP: Optimize selection option. The ChatGTP feedback is the following:

Let's try another code.

First, I have this table:

CREATE TABLE myvalues (
    id INT
)

Secondly,  I have this code to insert values from 1 to 100 in the table:

WHILE (@counter <= 100)
BEGIN
    INSERT INTO myvalues (id)
    VALUES (@counter)
    SET @counter = @counter + 1
END

Thirdly, we will use the ChatGTP optimize option and we will get this code:

DECLARE @counter INT = 1
WHILE (@counter <= 100)
BEGIN
    INSERT INTO myvalues (id)
    VALUES (@counter)
    SET @counter += 1
END

Me: 

Can you convert this code to use CTE? (include the previous code here)

ChatGPT: Sure! Here is the code to use a common table expression (CTE) to insert values from 1 to 100 into the "myvalues" table:

WITH counter_values AS (
    SELECT 1 AS val
    UNION ALL
    SELECT val + 1
    FROM counter_values
    WHERE val < 100
)
INSERT INTO myvalues (id)
SELECT val
FROM counter_values
OPTION (MAXRECURSION 0)

This code creates a CTE called "counter_values" that recursively generates a sequence of numbers from 1 to 100. It then inserts these values into the "id" column of the "myvalues" table using a single INSERT INTO…SELECT statement. Note that the OPTION (MAXRECURSION 0) clause is included to ensure that SQL Server allows for the maximum number of recursion iterations.

Also, we will try to Write documentation. Select the code, right-click, and select ChatGPT: Write documentation.

As you can see, ChatGPT creates nice documentation for the code.

Conclusion about ChatGPT in SQL Server

In this article, we saw how to install a ChatGPT extension in Visual Studio Code, and we also learned how to add the SQL Server extension. Also, we learned how to connect to ChatGPT using REST API. We need a key code to connect. As we discussed in previous forums, ChatGPT is still doing some mistakes, but I can feel the potential, and I know that it will improve a lot especially because Microsoft is helping a lot of OpenAI, so I think in the long run, it will change the software industry.