SQL Server Database Unit Testing using SQL Server Data Tools [SSDT]

In this article, we will see how to Unit Test SQL Server database using SQL Server Data Tools [SSDT]. For this demonstration, I am using SQL Server 2012 and Visual Studio 2013. But you can also use Visual Studio 2010 or Visual Studio 2012.

By using SSDT, we can now create unit tests for the database(s) in Visual Studio. When designing the database, we have to always make sure of the quality of our code and find out those problems/issues which may lead to incorrect execution and erroneous outputs. By using SQL Server unit testing, we can verify the outcomes of our database code (like Stored Procedures) by writing various test cases.
To start with, let’s first create a database script which will have some tables and stored procedures against which we can write our test cases. I have created a database with the name “PurchaseOrderDB”. I have created the following tables and stored procedures as listed below –

Tables –

• Customers
• Products
• Suppliers
• Orders

Stored Procedures –

• CreateCustomerProfile
• CreateNewOrder

Please have a closer look on the script which is shown here –

USE PurchaseOrderDB
GO
CREATE TABLE Customers
(
    CustomerID NVARCHAR(10) CONSTRAINT PK_CUSTOMERS PRIMARY KEY,
    ContactName NVARCHAR(50),
    Address NVARCHAR(200),
    City NVARCHAR(50),
    State NVARCHAR(20),
    Country NVARCHAR(20),
    EMailID NVARCHAR(50),
    ContactNo NVARCHAR(20)
)

CREATE TABLE Products
(
    ProductID INT CONSTRAINT PK_Products PRIMARY KEY,
    ProductName NVARCHAR(50),
    Description NVARCHAR(200),
    AvailableQty INT,
    UnitPrice DECIMAL(8,2)
)

CREATE TABLE Suppliers 
(
    SupplierID INT CONSTRAINT PK_Suppliers PRIMARY KEY,
    SupplierName NVARCHAR(50),
    Area NVARCHAR(10)
)

CREATE TABLE Orders
(
    OrderID INT CONSTRAINT PK_ORDERS PRIMARY KEY,
    OrderDate DATETIME,
    RequiredDate DATETIME,
    Qty INT,
    UnitPrice DECIMAL(8,2),
    ShippingDate DATETIME,
    ShippingCity NVARCHAR(20),
    CustomerID NVARCHAR(10) REFERENCES Customers(CustomerID),
    ProductID INT REFERENCES Products(ProductID),
    SupplierID INT REFERENCES Suppliers(SupplierID)
)
GO

CREATE PROCEDURE CreateCustomerProfile
(
    @p_CustomerID NVARCHAR(10),
    @p_ContactName NVARCHAR(50),
    @p_Address NVARCHAR(200),
    @p_City NVARCHAR(50),
    @p_State NVARCHAR(20),
    @p_Country NVARCHAR(20),
    @p_EMailID NVARCHAR(50),
    @p_ContactNo NVARCHAR(20)
)
AS
BEGIN
    INSERT INTO Customers VALUES
    (
        @p_CustomerID,
        @p_ContactName,
        @p_Address,
        @p_City,
        @p_State,
        @p_Country,
        @p_EMailID,
        @p_ContactNo
    )
END
GO

CREATE PROCEDURE CreateNewOrder
(
    @p_OrderID INT,
    @p_OrderDate DATETIME,
    @p_RequiredDate DATETIME,
    @p_Qty INT,
    @p_UnitPrice DECIMAL(8,2),
    @p_ShippingDate DATETIME,
    @p_ShippingCity NVARCHAR(20),
    @p_CustomerID NVARCHAR(10),
    @p_ProductID INT,
    @p_SupplierID INT
)
AS
BEGIN
    DECLARE @v_AvailableQty INT
    SELECT @v_AvailableQty=AvailableQty FROM Products WHERE ProductID=@p_ProductID
    BEGIN TRY
        IF(@v_AvailableQty>@p_Qty)
            BEGIN
                INSERT INTO Orders VALUES
                (
                    @p_OrderID,
                    @p_OrderDate,
                    @p_RequiredDate,
                    @p_Qty,
                    @p_UnitPrice,
                    @p_ShippingDate,
                    @p_ShippingCity,
                    @p_CustomerID,
                    @p_ProductID,
                    @p_SupplierID
                )
                UPDATE Products SET AvailableQty=@v_AvailableQty-@p_Qty WHERE ProductID=@p_ProductID
            END
        ELSE
            BEGIN
                PRINT 'SORRY!! Required quantity is more than the available stock'
            END
    END TRY
    BEGIN CATCH
        THROW 51012, 'Some Thing Went Wrong', 1;
    END CATCH
END

To create the database, open SQL Server Management Studio and connect to your SQL Server instance. Then right click the Databases in Object Explorer and click on New Database context menu. Name the database as “PurchaseOrderDB” as shown below –



We will create a database project using Visual Studio. Open your Visual Studio and click on New Project. This will bring up the New Project dialog box. Choose SQL Server. Now it will show you SQL Server Database Project. Name it “PurchaseOrderDBUnitTests” as shown below –



Once your project is ready, click on Projects menu and Import the script as shown below –



Follow the wizard steps to import the script file in which we have saved our T-SQL Statements for creating Tables and Stored Procedures. After successful import, go to your Solution Explorer and check the project. It should look like below –



Now let’s go to Project Properties and change the Connection String as shown below –



Now press “F5”. This will deploy the database into specified connection string. Your database will look like below



Now let’s add some data into our master tables like Products and Suppliers as shown below

INSERT INTO Products VALUES(1000,'Washing Machine','Sony',100,18000)
INSERT INTO Products VALUES(1001,'Oven','Sony',150,28000)
INSERT INTO Products VALUES(1002,'Hand Mixer','Samsung',100,18000)
INSERT INTO Products VALUES(1003,'Surface Tablet','Microsoft',30,29000)
INSERT INTO Products VALUES(1004,'Micromax Canvas 2','Micromax',400,10000)
GO
INSERT INTO Suppliers VALUES(100,'FedX','EAST')
INSERT INTO Suppliers VALUES(101,'FedX','WEST')
INSERT INTO Suppliers VALUES(102,'FedX','NORTH')
INSERT INTO Suppliers VALUES(103,'FedX','SOUTH')

It’s time to create a Unit Test Project for our Stored Procedures. Open SQL Server Object Explorer and expand the Projects folder. You will find the SQL Server Object Explorer in the View Menu.

Expand the Programmability > Stored Procedures folder. Right click the CreatCustomerProfile and click on “Create Unit Tests” as shown below –



This will bring up a “Create Unit Tests” dialog box. Choose all the stored procedures on which you want create a unit test. From the Output Project section, choose the language (like C#) while creating the project and name the project. After this name the class file as shown below –



As soon as you click the OK button, it will show you the “SQL Server Test Configuration” dialog box. Choose the connection string and change the other setting as per your choice.

Once the test configuration is over, click on the OK button. This will bring up the “StoredProceduresTest” design window. Now we will change the default values which are set to null. Provide some meaningful values as per your requirement.

After this, go to Test Conditions and delete the existing test condition. From the Test Conditions dropdown list, choose row count and click on ‘+’ green button to add the condition. In the properties window, set the Row Count property to “1”. It should look like the following –



Now go to Test Menu and open Test Explorer from the Windows menu. Right click the dbo_CreateCustomerProfileTest method and run the test. You should see the test result as shown below –



Try rerunning the same test and your test will be failed due to primary key exception. You can see the result in the Test Explorer.

Now let’s test CreateNewOrder stored procedure. From the StoredProceduresTest design window, choose the dbo_CreateNewOrderTest method. It will show you the test code. Let’s change the default values.
Also delete the default condition and set Row Count Condition by changing the Row Count Property to 1.
Now run the test by choosing the method dbo_CreateNewOrderTest from the Test Explorer. Your test should pass as shown below –



Let’s fail our test by changing the CustomerID which does not exist. Run the test and you will see your test has failed.



Summary – In this article, we saw how to perform Unit Testing on SQL Server Database using SQL Server Data Tools [SSDT].