In todays technological landscape, artificial intelligence has carved a significant niche for itself, offering unparalleled assistance in numerous domains. One such domain is database development, where the AI model, such as ChatGPT, has been gaining increasing recognition.
This article delves into how ChatGPT can help database developers streamline their work by aiding in the creation of Create, Read, Update, and Delete (CRUD) procedures for SQL Server tables. Through intelligent suggestions and automated responses, ChatGPT not only simplifies this vital process but also enhances productivity and efficiency, thereby redefining how database developers approach their tasks.
Additionally, we will validate the AI-generated code and compare it with the CRUD stored procedures generated by SQL Complete, a robust add-on designed for SQL Server database development.
- Download and install the AdventureWorks database
- Create CRUD procedures with the help of ChatGPT
To follow the scenario described in this article, you will need the following:
- ChatGPT account
- AdventureWorks database
- SQL Server Management Studio (SSMS) with SQL Complete downloaded and installed
Download and install the AdventureWorks database
- Download the AdventureWorks2019.bak file from the official Microsoft website.
- Open SSMS, connect to your SQL Server instance, and restore AdventureWorks2019 from the downloaded .bak file.
- Verify the database installation by connecting to it, going to Database/Object Explorer, and navigating across its objects: tables, views, stored procedures, etc.
Create CRUD procedures with the help of ChatGPT
CRUD operations in SQL Server refer to the basic database operations: Create, Read, Update, and Delete. These operations allow for the creation of new data records, retrieval of existing data, modification of data, and deletion of unwanted data, respectively.
Let us look at how you can leverage the power of AI to generate CRUD stored procedures in SQL Server.
CRUD stored procedures in SQL Server
To begin with, let’s find out what CRUD stored procedures are and how they can be used in SQL Server.
A CRUD stored procedure refers to a database procedure or routine that is designed to perform the basic Create, Read, Update, and Delete operations on a database table. CRUD is an acronym that stands for:
- Create: Inserts new data or records into the database table.
- Read: Retrieves or reads existing data or records from the database table.
- Update: Modifies or updates existing data or records in the database table.
- Delete: Removes or deletes existing data or records from the database table.
A CRUD stored procedure encapsulates the necessary SQL statements and logic to execute these operations in a structured and controlled manner. It provides a standardized approach for interacting with the database table, ensuring data integrity and consistent manipulation of data. CRUD stored procedures are commonly used in database development to simplify and streamline data management tasks.
CRUD stored procedures offer a reusable and centralized solution for managing database operations, making it easier to maintain and update the application code. They also enhance performance by reducing network traffic and improving query execution.
How ChatGPT can help generate CRUD stored procedures
Suppose, we want to create a CRUD Stored Procedure for the HumanResources.Department table in the AdventureWorks2019 database. Let us ask the AI for assistance.
Here is the code that ChatGPT has generated for us.
CREATE PROCEDURE dbo.Department_CRUD @DepartmentID INT = NULL, @Name NVARCHAR(50) = NULL, @GroupName NVARCHAR(50) = NULL, @ModifiedDate DATETIME = NULL, @Action NVARCHAR(10) AS BEGIN SET NOCOUNT ON; IF @Action = 'SELECT' BEGIN SELECT DepartmentID, Name, GroupName, ModifiedDate FROM HumanResources.Department WHERE (@DepartmentID IS NULL OR DepartmentID = @DepartmentID) AND (@Name IS NULL OR Name = @Name) AND (@GroupName IS NULL OR GroupName = @GroupName) AND (@ModifiedDate IS NULL OR ModifiedDate = @ModifiedDate) END ELSE IF @Action = 'INSERT' BEGIN INSERT INTO HumanResources.Department (Name, GroupName, ModifiedDate) VALUES (@Name, @GroupName, @ModifiedDate) END ELSE IF @Action = 'UPDATE' BEGIN UPDATE HumanResources.Department SET Name = @Name, GroupName = @GroupName, ModifiedDate = @ModifiedDate WHERE DepartmentID = @DepartmentID END ELSE IF @Action = 'DELETE' BEGIN DELETE FROM HumanResources.Department WHERE DepartmentID = @DepartmentID END END
Let us now check whether it is working properly. First of all, we run the code, and see that it is executed successfully, and a new procedure appears in the Object Explorer.
Next, to check whether the AI-generated stored procedure is valid, we execute its SELECT part adding the parameters.
Quite impressive, isn’t it?
Let’s delve deeper into exploring the SQL capabilities of ChatGPT and request its assistance in generating four separate stored procedures for each CRUD operation instead of a single stored procedure.
Here is the result we obtained.
And here is the code for the stored procedures for your convenience.
-- Create Procedure CREATE PROCEDURE dbo.sp_CreateDepartment @DepartmentID INT, @DepartmentName NVARCHAR(50), @GroupName NVARCHAR(50) AS BEGIN INSERT INTO HumanResources.Department (DepartmentID, DepartmentName, GroupName) VALUES (@DepartmentID, @DepartmentName, @GroupName) END -- Read Procedure CREATE PROCEDURE dbo.sp_ReadDepartment AS BEGIN SELECT * FROM HumanResources.Department END -- Update Procedure CREATE PROCEDURE dbo.sp_UpdateDepartment @DepartmentID INT, @DepartmentName NVARCHAR(50), @GroupName NVARCHAR(50) AS BEGIN UPDATE HumanResources.Department SET DepartmentName = @DepartmentName, GroupName = @GroupName WHERE DepartmentID = @DepartmentID END -- Delete Procedure CREATE PROCEDURE dbo.sp_DeleteDepartment @DepartmentID INT AS BEGIN DELETE FROM HumanResources.Department WHERE DepartmentID = @DepartmentID END
Shall we check it?
This time we get an error when trying to execute the code written by ChatGPT. It appears that it invented column names.
How to generate CRUD stored procedures using SQL Complete
SQL Complete is a powerful add-on tool for SQL Server database development. It is designed to enhance the productivity and efficiency of developers by providing intelligent code completion, code snippets, and SQL statement suggestions. SQL Complete offers features such as real-time syntax and error highlighting, code formatting, code navigation, and code analysis. It helps developers write SQL code faster, with fewer errors, and adhering to best practices. SQL Complete integrates seamlessly with popular database development tools like SQL Server Management Studio (SSMS) and provides a comprehensive set of tools to streamline the development process and improve productivity.
In addition to its numerous features, SQL Complete also enables the creation of CRUD procedures with ease. It provides a convenient and efficient way to generate the necessary SQL code for Create, Read, Update, and Delete operations on database tables. By utilizing SQL Complete, developers can accelerate the development process by automatically generating the code structure for CRUD procedures, saving time and reducing the chances of errors.
Let us look at how you can utilize the powers of SQL Complete for creating CRUD stored procedures.
To create a CRUD procedure, in Object Explorer, right-click on the database table and select SQL Complete > Script Table as CRUD. This will generate a code in a new SQL document.
Let us execute the code.
As you can see, the code has been executed without any errors, and as a result, four stored procedures are now visible in the Object Explorer. Using four separate stored procedures instead of a single large one offers several advantages in terms of organizing, reusing, maintaining, and optimizing your code in the long run.
In this article, we explored the capabilities of ChatGPT in generating CRUD stored procedures for SQL Server and compared it to the functionality of SQL Complete. While ChatGPT showcased its potential in assisting with CRUD procedure creation, SQL Complete demonstrated better accuracy in handling the task. However, it is important to note that ChatGPT is a remarkable tool that is continually being developed, and its potential for assisting developers is promising. As an AI-powered solution, ChatGPT offers unique advantages and can be a valuable asset in simplifying the development process.
Take your SQL Server database development to the next level by downloading and trying out SQL Complete today. Experience its powerful features and witness firsthand how it enhances your productivity, accuracy, and efficiency in writing SQL code. Download now and see the difference for yourself!