This is the second in a series of posts intended to get people up to speed using the tSQLt testing framework to test their stored procedures. If you missed it, check out the first post
here. We'll be continuing where that one leaves off.
Now that we've got Northwind setup, we're ready to start learning what tSQLt does for us, and how it does it. The main goal of tSQLt is to allow us to test our stored procedures. Since stored procedures often rely on data and we can't really count on that data being in a specific state before our test, we're first going to learn how to setup our tests.
First up: test classes. A test class is just a schema in SQL Server. We use them to group our tests together. You should choose the best method for your organization when it comes to naming your test classes. At one client we decided to create a test class for each stored procedure. That way all of the tests for that stored procedure could be easily contained within a single test class. If something changed in the stored procedure we'd only have to modify the tests that were in that test class. I'll be following that convention here so I'm going to create a test class for all of the tests I write that test the functionality of dbo.CustOrderHist. I do that by running the following code on the Northwind database:
EXEC tSQLt.NewTestClass 'CustOrderHist.Tests'
Now that our test class exists we're ready to set up our data so we can test our stored procedure. When your tests run, tSQLt will first check the test class (in this case CustOrderHist.Tests) for a stored procedure named "setup" (case insensitive). If such a stored procedure is found, it is executed before each test in the test class. That makes it a great place to start setting up our data for our tests.
First, let's create the Setup procedure:
CREATE PROCEDURE [CustOrderHist.Tests].[Setup]
AS
BEGIN
END
GO
By examining CustOrderHist we can see that it relies on data from the Products, [Order Details], Orders, and Customers tables. Since we rely on that data, we'll need to set up that data before each of our tests run. Fortunately, tSQLt makes that really easy for us. We just have to execute tSQLt.FakeTable for each table. Here's the code:
CREATE PROCEDURE [CustOrderHist.Tests].[Setup]
AS
BEGIN
EXEC tSQLt.FakeTable 'Products'
EXEC tSQLt.FakeTable 'Order Details'
EXEC tSQLt.FakeTable 'Orders'
EXEC tSQLt.FakeTable 'Customers'
END
The FakeTable procedure opens a transaction, renames the table being passed, then recreates a table with the same name and structure, but without any constraints, defaults, or triggers. After the code above runs, all four tables will be empty shells of their normal selves, allowing us to populate whatever data we want into them.
The stored procedure we're testing is pretty basic so that's all the setup we're going to need. Remember that from here on out all of the tests that we create in the CustOrderHist.Tests schema (test class) will first execute those FakeTable calls. Now we're ready to create our first test. For reference, this is what the CustOrderHist stored procedure actually looks like:
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName
The first thing we're going to test is whether we get any results when we pass in a customer id that should have results. Since a test in tSQLt is just a stored procedure we can create a test pretty darn easily. Here's what it looks like before we actually have any contents in our test:
CREATE PROCEDURE [CustOrderHist.Tests].[test Should Return Results When Records Exist For Customer]
AS
BEGIN
END
As long as the stored procedure name starts with "test", tSQLt will pick it up as a test when the tests run. Now that we have the test technically created, let's actually test something, shall we? In our setup procedure we faked four tables. Now we're going to populate those tables with some fake data to prove that our procedure under test does what we think it does:
CREATE PROCEDURE [CustOrderHist.Tests].[test Should Return Results When Records Exist For Customer]
AS
BEGIN
-- Arrange
INSERT INTO dbo.Products (ProductID, ProductName)
VALUES (1, 'First Product'), (2, 'Second Product'), (3, 'Third Product')
INSERT INTO dbo.Customers (CustomerID)
VALUES ('ABCDE'), ('FGHIJ'), ('KLMNO'), ('PQRST')
INSERT INTO dbo.Orders (OrderID, CustomerID)
VALUES (1, 'ABCDE'), (2, 'ABCDE'), (3, 'PQRST'), (4, 'ABCDE')
INSERT INTO dbo.[Order Details] (OrderID, ProductID, Quantity)
VALUES (1, 1, 10), (2, 3, 100), (3, 2, 1000), (4, 1, 503)
CREATE TABLE #Results (ProductName NVARCHAR(80), Quantity INT)
-- Act
-- Assert
END
You can see that in addition to populating fake data, I also created a temp table, which I'll use to store my results. Now that we're all setup, we can go ahead and execute the stored procedure, putting the results into the temp table:
CREATE PROCEDURE [CustOrderHist.Tests].[test Should Return Results When Records Exist For Customer]
AS
BEGIN
-- Arrange
INSERT INTO dbo.Products (ProductID, ProductName)
VALUES (1, 'First Product'), (2, 'Second Product'), (3, 'Third Product')
INSERT INTO dbo.Customers (CustomerID)
VALUES ('ABCDE'), ('FGHIJ'), ('KLMNO'), ('PQRST')
INSERT INTO dbo.Orders (OrderID, CustomerID)
VALUES (1, 'ABCDE'), (2, 'ABCDE'), (3, 'PQRST'), (4, 'ABCDE')
INSERT INTO dbo.[Order Details] (OrderID, ProductID, Quantity)
VALUES (1, 1, 10), (2, 3, 100), (3, 2, 1000), (4, 1, 503)
CREATE TABLE #Results (ProductName NVARCHAR(80), Quantity INT)
-- Act
INSERT INTO #Results
EXEC dbo.CustOrderHist 'ABCDE'
-- Assert
END
The last step is to check whether what we have in our results temp table is what we expect to have there:
CREATE PROCEDURE [CustOrderHist.Tests].[test Should Return Results When Records Exist For Customer]
AS
BEGIN
-- Arrange
INSERT INTO dbo.Products (ProductID, ProductName)
VALUES (1, 'First Product'), (2, 'Second Product'), (3, 'Third Product')
INSERT INTO dbo.Customers (CustomerID)
VALUES ('ABCDE'), ('FGHIJ'), ('KLMNO'), ('PQRST')
INSERT INTO dbo.Orders (OrderID, CustomerID)
VALUES (1, 'ABCDE'), (2, 'ABCDE'), (3, 'PQRST'), (4, 'ABCDE')
INSERT INTO dbo.[Order Details] (OrderID, ProductID, Quantity)
VALUES (1, 1, 10), (2, 3, 100), (3, 2, 1000), (4, 1, 503)
CREATE TABLE #Results (ProductName NVARCHAR(80), Quantity INT)
-- Act
INSERT INTO #Results
EXEC dbo.CustOrderHist 'ABCDE'
-- Assert
DECLARE @RowCount INT
SELECT @RowCount = COUNT(*) FROM #Results
EXEC tSQLt.AssertEquals 2, @RowCount
END
That last line runs a tSQLt procedure that compares the first parameter (expected result) to the second parameter (actual result). In this test we only wanted to see that the number of rows returned from the stored procedure was what we'd expect, and it was.
That's our first test. You run it like this:
EXEC tSQLt.Run CustOrderHist.Tests'
In order to fully test the CustOrderHist stored procedure, though, we'd need to write a few more tests. I've gone ahead and just written them and posted them
here to give you an idea of what they would look like.
