Today we are going to look at why a test might not be running. Before starting, if you don't have tSQLt setup, follow the instruction here: Unit testing in SQL Server, installing tSQLt and doing a quick test
First, if you didn't create a test database yet, let's create one and name it UnittestExamples, we will also create a simple table in that database
CREATE DATABASE UnittestExamples GO USE UnittestExamples GO CREATE TABLE SomeTable (ID int) Now connect to your tSQLt database USE tSQLt GO
Let's create two test classes
EXEC tSQLt.NewTestClass 'MyNewClass' EXEC tSQLt.NewTestClass 'MyNewestClass'
To see all the test classes in your tSQLt database, you can use the following query
SELECT Name, SchemaId FROM tSQLt.TestClasses;
Name SchemaId
MyNewClass 6
MyNewestClass 7
Now we will create a bunch of procs, these procs will test for the existance of a table in the UnittestExamples database
CREATE PROCEDURE MyNewClass.[ObjectExist] AS EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.SomeTable' GO CREATE PROCEDURE MyNewestClass.[ObjectExist] AS EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.SomeTable' GO CREATE PROCEDURE MyNewClass.[test ObjectExist] AS EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.SomeTable' GO CREATE PROCEDURE MyNewestClass.[test ObjectExist] AS EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.SomeTable' GO CREATE PROCEDURE [test ObjectExist] AS EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.SomeTable' GO
EXEC tSQLt.tSQLt.Run 'MyNewestClass'
+----------------------+
|Test Execution Summary|
+----------------------+
|No|Test Case Name |Result |
+--+----------------------------------+-------+
|1 |[MyNewestClass].[test ObjectExist]|Success|
-----------------------------------------------------------------------------
Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------
EXEC tSQLt.tSQLt.Run 'MyNewClass'
+----------------------+
|Test Execution Summary|
+----------------------+
|No|Test Case Name |Result |
+--+-------------------------------+-------+
|1 |[MyNewClass].[test ObjectExist]|Success|
-----------------------------------------------------------------------------
Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------
Now, let;s call all the classes in one shot, will that make a difference?
EXEC tSQLt.tSQLt.RunAll
+----------------------+
|Test Execution Summary|
+----------------------+
|No|Test Case Name |Result |
+--+-------------------------------+-------+
|1 |[MyNewClass].[test ObjectExist] |Success|
|2 |[MyNewestClass].[test ObjectExist] |Success|
-----------------------------------------------------------------------------
Test Case Summary: 2 test case(s) executed, 2 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------
Nope, only two out of five tests executed.
So what happened, why are only two of the five tests running? The reason is that each test has to be named so that the first part of the name is test. We created the following four test stored procedures
CREATE PROCEDURE MyNewClass.[ObjectExist] CREATE PROCEDURE MyNewestClass.[ObjectExist] CREATE PROCEDURE MyNewClass.[test ObjectExist] CREATE PROCEDURE MyNewestClass.[test ObjectExist]
As you can see the first two are not named so that the name starts with test. Renaming those so that they start with test will fix that. I myself have made this mistake many times in the beginning, hopefully this will save someone some time in the future
What about the 5th proc why didn't that run?
CREATE PROCEDURE [test ObjectExist]
As you can see that proc is named correctly, however it is not in the two test classes that we created. It is created in the default class which most likely will be dbo, You can actually execute this by calling the dbo class
EXEC tSQLt.tSQLt.Run 'dbo'
+----------------------+
|Test Execution Summary|
+----------------------+
|No|Test Case Name |Result |
+--+------------------------+-------+
|1 |[dbo].[test ObjectExist]|Success|
-----------------------------------------------------------------------------
Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------
So there you have it, the procs have to be named so that they start with test and the procs have to be created in a test class that the following query returns
SELECT Name, SchemaId FROM tSQLt.TestClasses;
Time to clean up the mess...... Here is how you can quickly clean up, this will drop the test class as well as all the objects in that class
EXEC tSQLt.DropClass 'MyNewClass' EXEC tSQLt.DropClass 'MyNewestClass'
This post is part of a Unit Testing series I created, you can find links to all the posts here: Unit testing in SQL Server, a list of all the posts