After Running the Examples – SMSS

Congratulations on executing your first tests!

This tutorial picks up after you’ve run the provided examples.
If you’re using SQL Test, click here to continue with the examples in the UI.

1. Fix the failing test

You’ll notice that there is a failing test case: [AcceleratorTests].[test ready for experimentation if 2 particles]

Let’s start by looking at the output from the failing test:

failed: Expected: <0> but was: <1>

This means that an incorrect value was returned from the code.

Test cases are implemented as stored procedures in a test class schema. Open up the test case stored procedure. You can find it in the Object Explorer under tSQLt_Example / Programmabilty / Stored Procedures. Right-click and choose “Modify” to script view it in a query editor window.

ALTER PROCEDURE 
  AcceleratorTests.[test ready for experimentation if 2 particles]
AS
BEGIN
  --Assemble: Fake the Particle table to make sure 
  --          it is empty and has no constraints
  EXEC tSQLt.FakeTable 'Accelerator.Particle';
  INSERT INTO Accelerator.Particle (Id) VALUES (1);
  INSERT INTO Accelerator.Particle (Id) VALUES (2);
  
  DECLARE @Ready BIT;
  
  --Act: Call the IsExperimentReady function
  SELECT @Ready = Accelerator.IsExperimentReady();
  
  --Assert: Check that 1 is returned from IsExperimentReady
  EXEC tSQLt.AssertEquals 1, @Ready;
  
END;

First, this test inserts two records into the Particle table. Then it checks that the IsExperimentReady function returns 1. According to the test output, it is instead returning 0.

Now modify the IsExperimentReady() function – it is under tSQLt_Example / Programmabilty / Functions / Scalar-valued Functions.

CREATE FUNCTION Accelerator.IsExperimentReady()
RETURNS BIT
AS
BEGIN 
  DECLARE @NumParticles INT;
  
  SELECT @NumParticles = COUNT(1) FROM Accelerator.Particle;
  
  IF @NumParticles > 2
    RETURN 1;

  RETURN 0;
END;

Do you see the bug? The line:

  IF @NumParticles > 2

should instead be:

  IF @NumParticles >= 2

Make the change to the function and run the tests again:
You can execute all tests by running:

EXEC tSQLt.RunAll;

You can also setup a keyboard shortcut for tSQLt.RunAll.

2. Write your own new test

Now that all the tests are passing, it is time to try out writing your own test case.

1. Create a test class

Test procedures in tSQLt are grouped into “test classes”. A test class is a schema that is specially marked by tSQLt. That way tSQLt knows how to find your test cases.

To create a new test class, open new Query Editor window and execute the following code:

EXEC tSQLt.NewTestClass 'TryItOut';
GO

2. Create a test case that fails (just to see what it looks like)

Next create a test procedure on this test class.

From the same Query Editor window execute the following code:

CREATE PROCEDURE TryItOut.[test this causes a failure]
AS
BEGIN
    EXEC tSQLt.Fail 'This is what a failure looks like';
END;
GO

3. Create a test case that passes (again, just to see what it looks like)

CREATE PROCEDURE TryItOut.[test this one passes]
AS
BEGIN
    DECLARE @sum INT;
    SELECT @sum = 1 + 2;

    EXEC tSQLt.AssertEquals 3, @sum;
END
GO

4. Run all of the tests

Again, you can execute all the tests by using tSQLt.RunAll:

EXEC tSQLt.RunAll;
GO

You will see the following output:

[TryItOut].[test this causes a failure] failed: This is what a failure looks like

+---------------------+
|Test Execution Sumary|
+---------------------+

|No|Test Case Name                         |Result |
+--+---------------------------------------+-------+
|1 |[TryItOut].[test this one passes]      |Success|
|2 |[TryItOut].[test this causes a failure]|Failure|
-----------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Line 1
Test Case Summary: 2 test case(s) executed, 1 succeeded, 1 failed, 0 errored.
-----------------------------------------------------------------------------

5. When you’re done experimenting with this test class, you can easily clean it up by running the following procedure

EXEC tSQLt.DropClass 'TryItOut';
GO