In this post my aim is to introduce the basic tSQLt assertions. The examples should be enough to get you going with assertions. My assumption is that you have at least some experience writing unit tests with any unit testing framework.
tSQLt.Fail
CREATE PROCEDURE [UnitTest_FirstGo].[test Fail]
AS
BEGIN
EXEC tSQLt.Fail 'This test should fail and display this message';
END
tSQLt.AssertEquals
CREATE PROCEDURE [UnitTest_FirstGo].[test AssertEquals]
AS
BEGIN
--Assemble
DECLARE @expected int
DECLARE @actual int
SET @expected = 1;
SET @actual = 0;
--Act
--Assert
EXEC tSQLt.AssertEquals @expected, @actual, 'The is a TEST FAILURE MESSAGE!'
END
tSQLt.AssertEquals with System Under Test (SUT)
First we need to create something to represent the SUT
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_SUM]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_SUM]
GO
CREATE FUNCTION fn_SUM
(
@a int,
@b int
)
RETURNS int
BEGIN
RETURN @a+@b;
END
and now the test
CREATE PROCEDURE [UnitTest_FirstGo].[test AssertEquals with SUT]
AS
BEGIN
--Assemble
DECLARE @expected int
DECLARE @actual int
SET @expected = 3;
--Act
SELECT @actual = dbo.fn_SUM(1,2);
--Assert
EXEC tSQLt.AssertEquals @expected, @actual
END
tSQLt.AssertEqualsString
CREATE PROCEDURE [UnitTest_FirstGo].[test AssertEqualsString]
AS
BEGIN
--Assemble
DECLARE @expected varchar(20)
DECLARE @actual varchar(20)
SET @expected = 'hello world';
SET @actual = 'goodbye world';
--Act
--Assert
EXEC tSQLt.AssertEqualsString @expected, @actual, 'The is a TEST FAILURE MESSAGE!'
END
tSQLt.AssertEqualsTable
This one takes a bit of time to get your head around, but once you get going and when used in conjunction with Fake tables it's really awesome.
CREATE PROCEDURE [UnitTest_FirstGo].[test AssertEqualsTable]
AS
BEGIN
--Assemble
if exists(select *
from information_schema.tables
where table_schema='dbo' and table_name='tablea')
begin
drop table dbo.TableA;
create table dbo.TableA (id int not null);
end
if exists(select *
from information_schema.tables
where table_schema='dbo' and table_name='tableb')
begin
drop table dbo.TableB;
create table dbo.TableB (id int not null);
end
insert tablea (id) values (1);
insert tablea (id) values (2);
insert tablea (id) values (3);
insert tableb (id) values (1);
--insert tableb (id) values (2);
insert tableb (id) values (3);
--Act
-- nothing to do here
--Assert
exec tSQLt.AssertEqualsTable 'TableA', 'TableB'
END
Uncomment the commented out insert to make the test pass.
The failure message attempts to point you to what failed showing which rows matched and which failed to match.
tSQLt.AssertObjectEquals
For this test we once again need some setup (i.e. we need to create an object to test if it exists)
SELECT OBJECT_ID (N'dbo.fn_SUM', N'F')
IF OBJECT_ID (N'dbo.fn_SUM', N'F') IS NOT NULL
DROP FUNCTION dbo.fn_sum
GO
CREATE FUNCTION fn_SUM
(
@a int,
@b int
)
RETURNS int
BEGIN
RETURN @a+@b;
END
And now the test
CREATE PROCEDURE [UnitTest_FirstGo].[test AssertObjectExists]
AS
BEGIN
--Assert
EXEC tSQLt.AssertObjectExists 'fn_SUM'
END
tSQLt.AssertResultSetsHaveSameMetaData
CREATE PROCEDURE [UnitTest_FirstGo].[test AssertObjectExists]
AS
BEGIN
--Assemble
if exists(select *
from information_schema.tables
where table_schema='dbo' and table_name='tablea')
begin
drop table dbo.TableA;
create table dbo.TableA (id int not null);
end
if exists(select *
from information_schema.tables
where table_schema='dbo' and table_name='tablec')
begin
drop table dbo.TableC;
create table dbo.TableC (id int not null, name varchar(50) not null);
end
insert tablea (id) values (1);
insert tablea (id) values (2);
insert tablea (id) values (3);
insert tablec (id, name) values (5, 'andrew')
--Act
-- nothing to do here
--Assert
exec tSQLt.AssertResultSetsHaveSameMetaData
'select * from tablea',
'select * from tablec'
END
That's it for now. I hope to get some time to look at how to isolate dependencies in the near future.
