How GPT Chat Can Help You Generate CRUD Procedures for SQL Server Tables

In today’s 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.

Contents

• Prerequisites
• Download and install the AdventureWorks database
• Create CRUD procedures with the help of ChatGPT
  • CRUD Stored Procedures in SQL Server
  • How ChatGPT can help generate CRUD stored procedures
  • How to generate CRUD stored procedures using SQL Complete
• Conclusion

Prerequisites

To follow the scenario described in this article, you will need the following:

1. ChatGPT account
2. AdventureWorks database
3. SQL Server Management Studio (SSMS) with SQL Complete downloaded and installed

Download and install the AdventureWorks database

1. Download the AdventureWorks2019.bak file from the official Microsoft website.
2. Open SSMS, connect to your SQL Server instance, and restore AdventureWorks2019 from the downloaded .bak file.
3. 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.

Conclusion

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!