After Running the Examples - SQL Test • tSQLt - Database Unit Testing for SQL Server

Congratulations on executing your first tests!

This tutorial picks up after you’ve run the provided examples with SQL Test. Get it here if you don’t have it!
If you’re not using SQL Test, start writing tests in the Query Editor.

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 by double clicking on the failing test:

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.

ALTER 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 by pressing Shift+Alt+X. All the tests should be passing now:

2. Write your own new test

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

To create a new test, follow these steps:

In the SQL Test Pane, click “New Test”.
Fill in the details of the test case:
1. Enter a name for the test. For this example, enter: test add two numbers
2. Enter the name of a test class (a test class is a grouping of related test cases). For this example, enter: TryItOut.
3. Choose which database to put the test. For this example, choose tSQLt_Example
4. Click OK
5. A new Query Editor window will open with a standard template for creating a new test

Fill in the details of the new test in the Query Editor and press F5 to change the procedure in the database:

ALTER PROCEDURE TryItOut.[test add two numbers]
AS
BEGIN
    DECLARE @sum INT;
    SELECT @sum = 1 + 2; -- Normally, you would call your own code such as a function, view or stored proc

    EXEC tSQLt.AssertEquals 3, @sum;
END
GO

Run all of the tests
Press Shift+Alt+X. You should now see the following in the SQL Test Pane:
When you’re done experimenting with this test class, you can easily clean it up and delete everything in it by executing the following script in a new Query Editor window:
```
EXEC tSQLt.DropClass 'TryItOut';
GO
```
After executing this script, refresh the tests in the SQL Test Pane.

Share and Enjoy: