The tSQLt unit test framework that we use to do our DB tests does not allow for disabled tests, this is because each test is just a stored proc. Right now the only way is to either rename the proc so it doesn’t start with test or to remove the test
I was thinking about this in the shower this morning….here is what I came up with
Here is a quick way how to prevent a test from running when the tSQLt tests run.
Put the code below into each unit test that you want disabled, you should put this code right after the CREATE PROCEDURE ProcName AS part
This code will pick the correct name and schema, so you can use the same exact code in each test
DECLARE @IsTestDisabled bit = 1 -- set this to 1 if you don't want the test to run
IF @IsTestDisabled =1
BEGIN
DECLARE @SchemaName varchar(1000)
SELECT @SchemaName = QUOTENAME(SCHEMA_NAME(schema_id)) +'.'
FROM sys.procedures WHERE object_id = @@procid
PRINT 'Disabled Test: ' + @SchemaName + QUOTENAME(OBJECT_NAME(@@procid))
RETURN
END
So if we grab the
example test from the tSQLt website
We would change it from this
CREATE PROCEDURE testFinancialApp.[test that ConvertCurrency converts using given conversion rate]
AS
BEGIN
DECLARE @actual MONEY;
DECLARE @rate DECIMAL(10,4); SET @rate = 1.2;
DECLARE @amount MONEY; SET @amount = 2.00;
SELECT @actual = FinancialApp.ConvertCurrency(@rate, @amount);
DECLARE @expected MONEY; SET @expected = 2.4; --(rate * amount)
EXEC tSQLt.AssertEquals @expected, @actual;
END;
GO
To this
CREATE PROCEDURE testFinancialApp.[test that ConvertCurrency converts using given conversion rate]
AS
DECLARE @IsTestDisabled bit = 1 -- set this to 1 if you don't want the test to run
IF @IsTestDisabled =1
BEGIN
DECLARE @SchemaName varchar(1000)
SELECT @SchemaName = QUOTENAME(SCHEMA_NAME(schema_id)) +'.'
FROM sys.procedures WHERE object_id = @@procid
PRINT 'Disabled Test: ' + @SchemaName + QUOTENAME(OBJECT_NAME(@@procid))
RETURN
END
BEGIN
DECLARE @actual MONEY;
DECLARE @rate DECIMAL(10,4); SET @rate = 1.2;
DECLARE @amount MONEY; SET @amount = 2.00;
SELECT @actual = FinancialApp.ConvertCurrency(@rate, @amount);
DECLARE @expected MONEY; SET @expected = 2.4; --(rate * amount)
EXEC tSQLt.AssertEquals @expected, @actual;
END;
GO
Now you will get the following in your tSQLt test run output
Disabled Test: [testFinancialApp].[test that ConvertCurrency converts using given conversion rate]
This will be right above this text
+----------------------+
|Test Execution Summary|
+----------------------+
I want this printed so we know we have disabled tests when we get a email whenever Jenkins kicks off these tests…..
To enable the test again, all you have to do is change the value from 1 to 0 here. So instead of
DECLARE @IsTestDisabled bit = 1
You would make it
DECLARE @IsTestDisabled bit = 0
