Some reasons your tSQLt test is not executing

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
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

Now it is time to run our tests,

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