How ChatGPT Can Help Database Developers Write Unit Tests for SQL Server

We have already explored the capabilities of the much-hyped ChatGPT in terms of writing SQL JOIN queries and retrieving data from MySQL databases. Now the time has come to take one more step further and scrutinize a more narrow-focused yet crucial topic for database developers—unit testing. We’ll see how ChatGPT fares when it comes to writing unit tests for various database objects, and then we’ll run the output using one of the integrated features of dbForge Studio for SQL Server called Unit Test (which is also available as an SSMS add-in). That said, fasten your seatbelts, it’s going to be a curious ride.

CONTENTS

• Prerequisites
• Download and install dbForge Studio for SQL Server
• Download and install dbForge Unit Test
• Download and install AdventureWorks2019
• Run unit tests on a table
• Run unit tests on a stored procedure
• Run unit tests on a function
• Run unit tests on a view

Prerequisites

First and foremost, let’s make sure that we’ve got all of our prerequisites at hand. To get started, we’ll need the following:

• An active ChatGPT account – we’re going to ask it to write us unit tests and see how well it fares.
• dbForge Studio for SQL Server – an all-in-one IDE that covers nearly all aspects of work with SQL Server databases, where we’ll run ChatGPT’s output and see if it works.
• Alternatively, you can use SQL Server Management Studio (a.k.a. SSMS) – the basic IDE for SQL Server databases. In that case, you will also need to install dbForge Unit Test – an add-in that will actually make it possible to work with unit tests, since the default SSMS lacks this functionality.
• The AdventureWorks2019 sample database – the go-to database for testing and demo purposes.

These prerequisites will make you ready to take the same journey as the one we’re about to begin. Now let us help you a bit with installing dbForge Studio/dbForge Unit Test (whichever you prefer) and AndventureWorks2019.

Download and install dbForge Studio for SQL Server

You can download dbForge Studio here for a free 30-day trial. Once you download the installation file, just open it and follow the instructions of the installation wizard. It will be so easy that you won’t even need a manual.

Download and install dbForge Unit Test

Alternatively, if you prefer using SSMS, you can download dbForge Unit Test as part of dbForge SQL Tools, a bundle of 15 SSMS add-ins and standalone apps for SQL Server development, management, and administration. Similarly to the Studio, you only need to open the downloaded installation file and follow the wizard.

There is one difference, though. During the installation, you will be able to specify the individual products that you want to install, so you can select Unit Test only; or, if you wish, you can install the entire bundle, since there are a lot of useful goodies to be found and explored there. The bundle is also available for a free 30-day trial, so the try-before-you-by principle is firmly in place.

After the installation, the Unit Test menu will appear on the SSMS menu bar.

Download and install AdventureWorks2019

Now it’s time to install our sample database.

1. Download the AdventureWorks2019.bak file (you can use the direct download here) from the official website.
2. Open dbForge Studio (or SSMS), connect to your SQL Server instance, and restore AdventureWorks2019 from the downloaded .bak file.
3. Make sure that the restoration of the database has been successful by connecting to it, going to Database/Object Explorer, and navigating across its objects: tables, views, stored procedures, and so on.

Note: If you decide to give it a try, make sure you install the tSQLt framework on your database beforehand. And if you would like to get more information about running your tests in Unit Test, refer to Running SQL database unit tests. This functionality in dbForge Studio and dbForge Unit Test will be identical.

Now we’re all set up to pay ChatGPT a visit and see it in action!

Run unit tests on a table

Let’s start by asking ChatGPT to generate unit tests for the Person.Person table of the AdventureWorks2019 sample database.

For your convenience, here’s the code to take a closer look at.

-- Load the tSQLt framework
EXEC tSQLt.NewTestClass 'TestPerson';

-- Create a test procedure for checking if Person.Person table exists
CREATE PROCEDURE TestPerson.[test if Person.Person table exists]
AS
BEGIN
  -- Assert that the Person.Person table exists
  EXEC tSQLt.AssertObjectExists 'Person.Person';
END;
GO

-- Create a test procedure for checking if the Person.Person table has the correct columns
CREATE PROCEDURE TestPerson.[test Person.Person columns]
AS
BEGIN
  -- Define the expected columns of the table
  DECLARE @ExpectedColumns TABLE (ColumnName NVARCHAR(100));

  INSERT INTO @ExpectedColumns (ColumnName)
  VALUES ('BusinessEntityID'), ('PersonType'), ('NameStyle'), ('Title'), ('FirstName'), ('MiddleName'), ('LastName'), ('Suffix'), ('EmailPromotion'), ('AdditionalContactInfo'), ('Demographics'), ('rowguid'), ('ModifiedDate');

  -- Assert that the Person.Person table has the expected columns
  EXEC tSQLt.AssertEqualsTable 'Person.Person', @ExpectedColumns;
END;
GO

-- Create a test procedure for checking if the Person.Person table has any records
CREATE PROCEDURE TestPerson.[test Person.Person has records]
AS
BEGIN
  -- Assert that the Person.Person table has at least one record
  EXEC tSQLt.AssertRowCountGreaterThan 'Person.Person', 0;
END;
GO

-- Create a test procedure for checking if the Person.Person table has any duplicate records
CREATE PROCEDURE TestPerson.[test Person.Person has no duplicate records]
AS
BEGIN
  -- Assert that the Person.Person table has no duplicate records
  EXEC tSQLt.AssertNoDuplicateRows 'Person.Person';
END;
GO

-- Run all the tests in the TestPerson test class
EXEC tSQLt.RunTestClass 'TestPerson';

Now let’s go to dbForge Studio and see how it works via Unit Test. Note that the line EXEC tSQLt.NewTestClass 'TestPerson'; is executed automatically once a directory for unit tests is created.

That said, we create a test that shows whether our table actually exists.

Then we insert the code suggested by ChatGPT.

And now we run the test. As you can see, it’s successful.

Let’s try another case. Say, let’s check whether our Person.Person table has correct, expected columns.

We insert the code again.

This time we can see that ChatGPT’s suggestion wasn’t all that flawless, and the test returns an error.

Let’s take a look at the code and fix it as follows.

CREATE PROCEDURE TestPerson.[test Person.Person columns]
AS
BEGIN
  -- Define the expected columns of the table
 DECLARE @ExpectedColumns VARCHAR(8000) = 'BusinessEntityID,PersonType,NameStyle,Title,FirstName,MiddleName,LastName,Suffix,EmailPromotion,AdditionalContactInfo,Demographics,rowguid,ModifiedDate';

  -- Create a temporary table to store the actual columns of the Person.Person table
 DECLARE @ActualColumns VARCHAR(8000);

 SELECT @ActualColumns = STRING_AGG(c.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY c.ORDINAL_POSITION)
   FROM INFORMATION_SCHEMA.COLUMNS c
   WHERE TABLE_NAME = 'Person' AND c.TABLE_SCHEMA='Person';

  -- Assert that the Person.Person table has the expected columns
 EXEC tSQLt.AssertEquals @ExpectedColumns, @ActualColumns, 'The Sales.vSalesPerson view does not have the expected columns.';
END;
GO

Now let’s go back to dbForge Studio and insert our fixed code.

Then we run it. Success!

How about one more case? Let’s see whether the Person.Person table is empty or contains any records.

We insert the code.

We’ve got an error. The suggested procedure does not exist. Actually, ChatGPT thinks this procedure must be there by default, yet it isn’t so.

To make it work, we tweak the code.

CREATE PROCEDURE TestPerson.[test Person.Person has records]
AS
BEGIN
 SET NOCOUNT ON;

    BEGIN TRY
        -- Check if any data exists in the Person.Person table
        IF NOT EXISTS(SELECT 1 FROM Person.Person)
        BEGIN
            -- No data exists, throw a custom exception
            THROW 50001, 'No data found in Person.Person table', 1;
        END
    END TRY
    BEGIN CATCH
        -- Handle the exception
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        RAISERROR(@ErrorMessage, 16, 1);
        RETURN;
    END CATCH
END;
GO

Then we go to the Studio and insert it.

And so we run it – now it works!

Okay, since ChatGPT provided us with four unit test examples, let’s have a look at the final one and see whether our Person.Person table has any duplicate records.

We insert the suggested code.

Once again we’ve got a problem. ChatGPT has suggested a procedure that does not exist.

Let’s make corrections accordingly.

CREATE PROCEDURE TestPerson.[test Person.Person has no duplicate records]
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS(
        SELECT FirstName, LastName, MiddleName, COUNT(*) AS NumDuplicates
        FROM Person.Person
        GROUP BY FirstName, LastName, MiddleName
        HAVING COUNT(*) > 1
    )
    BEGIN
        -- Duplicates exist, show an error message
        RAISERROR('Duplicate rows found in Person.Person table', 16, 1);
        RETURN;
    END
    ELSE
    BEGIN
        -- No duplicates exist, return a message
        SELECT 'No duplicates found in Person.Person table' AS Result;
    END
END
GO

Back in the Studio, we insert the code.

We’ve got an error. This wasn’t unexpected, because the table contains some identical data, and we don’t really know whether it’s an actual duplicate or not.

To improve the table structure, we can suggest adding yet another field—for instance, date of birth. And if we group by that date of birth, we’ll get improved results that will help us clearly understand whether we’re dealing with the same person or not.

As it is, we can identify duplicates in the Person.Person table using the following SELECT query.

SELECT FirstName, LastName, MiddleName, COUNT(*) AS NumDuplicates
	FROM Person.Person
	GROUP BY FirstName, LastName, MiddleName
	HAVING COUNT(*) > 1
	ORDER BY NumDuplicates DESC, LastName, FirstName;

In our case, we can even find up to four different people with the same first and last names.

Run unit tests on a view

That was a curious experiment, but let’s move on from tables to views. Now we’ll ask ChatGPT to write unit tests for the Sales.SalesPerson view from the same AdventureWorks2019 sample database.

That should be enough. Back in the Studio, we’ll get ourselves a new test class. And first of all, we’ll check whether our view actually exists.

Now let’s insert the code suggested by ChatGPT and run it.

Success! This time the code needs no tweaking.

Now let’s get another test that will verify that our view returns the expected columns.

We insert the code…

Okay, here we have an error. There’s an incorrect expected result in the columns and an incorrect data type.

To make it work, we tweak the code as follows.

CREATE PROCEDURE TestView.[test SalesTest.vSalesPerson.testViewColumns]
AS
BEGIN
    -- Arrange
    DECLARE @ExpectedColumns VARCHAR(8000) = 'BusinessEntityID,Title,FirstName,MiddleName,LastName,Suffix,JobTitle,PhoneNumber,PhoneNumberType,EmailAddress,EmailPromotion,AddressLine1,AddressLine2,City,StateProvinceName,PostalCode,CountryRegionName,TerritoryName,TerritoryGroup,SalesQuota,SalesYTD,SalesLastYear';

    -- Act
    DECLARE @ActualColumns VARCHAR(8000);
    SELECT @ActualColumns = STRING_AGG(c.name, ',') WITHIN GROUP (ORDER BY c.column_id)
    FROM sys.views AS v
    INNER JOIN sys.columns AS c ON v.object_id = c.object_id
    WHERE v.name = 'vSalesPerson';

    -- Assert
    EXEC tSQLt.AssertEquals @ExpectedColumns, @ActualColumns, 'The Sales.vSalesPerson view does not have the expected columns.';
END;
GO

Then we take it to the Studio…

And so we run it again. Success!

Run unit tests on a function

Now let’s get ChatGPT to write unit tests for a function.

Again, we create a new test class along with a new test that will help us check the returned product price.

Then we insert the code suggested by ChatGPT.

Then we run it to get another error. ChatGPT has missed one function parameter and thus hasn’t declared it. The expected price result is also incorrect. The product with an ID that equals 1 doesn’t even exist in the table, and it can’t have a price of 3578.2700.

Okay, time to tweak the code.

CREATE PROCEDURE CheckFunction.[test MyUnitTest_1]
AS
BEGIN
    -- Arrange
    DECLARE @ProductID INT = 707;
    DECLARE @OrderDate DATE = CURRENT_TIMESTAMP;
    DECLARE @ExpectedPrice MONEY = 0.00;

    -- Act
    SELECT @ExpectedPrice = [dbo].[ufnGetProductListPrice](@ProductID, @OrderDate);

    -- Assert
    EXEC tSQLt.AssertEquals @Expected = 34.99, @Actual = @ExpectedPrice, @Message = 'Price for product ID 1 is incorrect';

END;
GO

Let’s insert it into the Studio…

And see what happens if we run it. Success!

Now let’s take a look at another case and check whether any of our product prices have NULL values. Here we create a new test.

After we insert and run the code, we’ve got yet another error. There is an undeclared variable and a missed parameter.

To fix that, we tweak the code.

CREATE PROCEDURE CheckFunction.[test CheckPriceOnNULL]
AS
BEGIN
    -- Arrange
    DECLARE @ProductID INT = 99999;
    DECLARE @OrderDate DATE = CURRENT_TIMESTAMP;
    DECLARE @ExpectedPrice MONEY = NULL;

    -- Act
    SELECT @ExpectedPrice = [dbo].[ufnGetProductListPrice](@ProductID,@OrderDate);

    -- Assert
    EXEC tSQLt.AssertEquals @Expected = @ExpectedPrice, @Actual = NULL, @Message = 'Price for invalid product ID is not NULL';

END;

GO

Then we insert it back.

And we run our test. As you can see, it’s successful.

Finally, let’s run both tests at once to make sure everything works well.

Run unit tests on a stored procedure

Let’s finish our marathon by asking ChatGPT to write us a few unit tests for a stored procedure.

We create yet another new test class alongside a unit test that takes the result returned by the procedure and checks whether it matches the expected result. A mismatch, naturally, will return an error.

We insert the code suggested by ChatGPT…

Here we can already notice that we don’t have the input parameter DECLARE @CheckDate DATETIME = ‘2023-04-05 18:40:28.215’. Other missing things include the variable and the structure of the table that we’ll use to verify the result. Let’s make some corresponding adjustments in the code.

CREATE PROCEDURE CheckProcedure.[test Procedure returns any rows]
AS
BEGIN
    -- Assemble
    DECLARE @ProductAssemblyID INT = 800
    DECLARE @CheckDate DATETIME = '2023-04-05 18:40:28.215'

    -- Act
    CREATE TABLE #actual_result (
        [ProductAssemblyID] [int] NOT NULL,
        [ComponentID] [int] NOT NULL,
        [ComponentDesc] [nvarchar](50) NOT NULL,
        [TotalQuantity] [decimal](8, 2) NOT NULL,
        [StandardCost] [money] NOT NULL,
        [ListPrice] [money] NOT NULL,
        [BOMLevel] [smallint] NOT NULL,
        [RecursionLevel] [int] NOT NULL
    );
    INSERT INTO #actual_result EXEC dbo.uspGetBillOfMaterials @StartProductID = @ProductAssemblyID, @CheckDate = @CheckDate

    -- Assert
    DECLARE @ExpectedRowCount INT = 87 -- Update the expected row count
    DECLARE @ActualRowCount INT = (SELECT COUNT(*) FROM #actual_result)

    EXEC tSQLt.AssertRowCount @Expected = @ExpectedRowCount, @Actual = @ActualRowCount
END;
GO

Now let’s take it to the Studio.

Finally, we run the code… and it works!

Okay, you must be a bit tired already, so we’ll stop here. As you can see, ChatGPT never guarantees completely error-free code, which is only natural, so make sure you inspect its output and make amendments, if necessary. Other than that, it’s a rather promising assistant when it comes to giving you something to start with.

Download dbForge tools for SQL Server for a free 30-day trial today!

Now you know that ChatGPT can be helpful with yet another typical task of a database developer, and that our tools can run ChatGPT’s output—properly reviewed and adjusted, of course—and yield results in no time.

That said, let us invite you once more to download dbForge Studio, an IDE that has virtually everything you need to facilitate effective development, management, and administration of SQL Server databases.

Alternatively, you can download dbForge Unit Test (alongside the entire SQL Tools bundle) and put it to good use in your daily work. Besides Unit Test, you’ll get other tools and SSMS add-ins that will help you do the following:

• Write SQL code faster with context-aware completion, formatting, and refactoring
• Build queries on diagrams with no coding
• Debug SQL scripts, functions, triggers, and stored procedures
• Compare and synchronize database schemas and table data
• Generate realistic test data
• Link your databases to the most widely used version control systems
• Monitor server performance
• Fix index fragmentation
• Automate recurring tasks from the command line
• Build a CI/CD cycle to streamline your database development

Download our tools for SQL Server and see them in action—we bet your routine work will never be quite the same afterwards!