It seems that the SQL test framework tSQLt does not allow to use an assertion such as tSQLt.AssertEqualsTableSchema after a TRY/CATCH block that generates an error. If I try that, the test generates an error instead of a pass/fail result. On the other hand, a simpler assertion such as tSQLt.AssertEquals works in such a case, passing the test.
I would like to know if there is a way to use such assert satements in this case, or if it is a fundamental limitation of tSQLt and it does not work.
This code reproduces the problem:
EXEC tSQLt.NewTestClass @ClassName = N'errtest';
GO
CREATE OR ALTER PROCEDURE errtest.test_simple
AS
BEGIN
PRINT('Executing errtest.test_simple');
CREATE TABLE Actual (
A INT,
B NVARCHAR(10));
CREATE TABLE Expected (
A INT,
B NVARCHAR(10));
BEGIN TRY
DECLARE @IntegerVariable AS INT;
SET @IntegerVariable = 'a string';
PRINT('There were no errors.');
END TRY
BEGIN CATCH
PRINT('An error happened.');
END CATCH
DECLARE @AssertType NVARCHAR(100);
SET @AssertType = 'AssertAssertEqualsTableSchema';
--SET @AssertType = 'AssertEquals';
IF @AssertType = 'AssertEquals' BEGIN
PRINT('ASSERT: Equals for 2 INTs');
EXEC tSQLt.AssertEquals @Expected = 1
,@Actual = 1
,@Message = N'no'
END
ELSE BEGIN
PRINT('ASSERT: EqualsTableSchema');
EXEC tSQLt.AssertEqualsTableSchema @Expected = N'Expected'
,@Actual = N'Actual'
,@Message = N'Hallo'
END
END
GO
EXEC tSQLt.Run 'errtest.test_simple';
If this is run with tSQLt installed, I get following result:
Executing errtest.test_simple
An error happened.
ASSERT: EqualsTableSchema
[errtest].[test_simple] failed: (Error) The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.[16,1]{tSQLt.AssertEqualsTableSchema,7} (There was also a ROLLBACK ERROR --> The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.{tSQLt.Private_RunTest,187})
+----------------------+
|Test Execution Summary|
+----------------------+
|No|Test Case Name |Dur(ms)|Result|
+--+-----------------------+-------+------+
|1 |[errtest].[test_simple]| 3|Error |
----------------------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Line 47
Test Case Summary: 1 test case(s) executed, 0 succeeded, 0 skipped, 0 failed, 1 errored.
----------------------------------------------------------------------------------------
However, if I uncomment the line --SET @AssertType = 'AssertEquals';, the simpler assertion tSQLt.AssertEquals runs without errors and passes the test:
Executing errtest.test_simple
An error happened.
ASSERT: Equals for 2 INTs
+----------------------+
|Test Execution Summary|
+----------------------+
|No|Test Case Name |Dur(ms)|Result |
+--+-----------------------+-------+-------+
|1 |[errtest].[test_simple]| 7|Success|
----------------------------------------------------------------------------------------
Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 skipped, 0 failed, 0 errored.
----------------------------------------------------------------------------------------
Here is a list of which assertion works and which do not work in the above example.
Working: AssertEquals, AssertEqualsString, AssertNotEquals, AssertObjectDoesNotExist, AssertObjectExists, Fail, (no assert used) → test passes
Not working: AssertEqualsTableSchema, AssertEmptyTable, AssertEqualsTable, AssertLike, AssertResultSetsHaveSameMetaData
The problem is that tSQLt starts a transaction - this is needed so faking objects can be rolled back and any helper objects it creates are removed. But errors in try catch can doom the transaction. tSQLt.AssertEqualsTableSchema attempts to insert to tables tSQLt.Private_AssertEqualsTableSchema_Expected and tSQLt.Private_AssertEqualsTableSchema_Actual which would write to the log file hence the error.
As a workaround you can do your own check in a manner that does not touch the transaction log. Example below (based on the SELECT that tSQLt does)
IF EXISTS (SELECT *
FROM (
SELECT
C.object_id,
RANK()OVER(PARTITION BY C.object_id ORDER BY C.column_id) AS col_ordinal,
C.name,
CAST(C.system_type_id AS NVARCHAR(MAX))+QUOTENAME(TS.name) system_type_id,
CAST(C.user_type_id AS NVARCHAR(MAX))+CASE WHEN TU.system_type_id<> TU.user_type_id THEN QUOTENAME(SCHEMA_NAME(TU.schema_id))+'.' ELSE '' END + QUOTENAME(TU.name) user_type_id,
C.max_length,
C.precision,
C.scale,
C.collation_name,
C.is_nullable
FROM sys.columns AS C
JOIN sys.types AS TS
ON C.system_type_id = TS.user_type_id
JOIN sys.types AS TU
ON C.user_type_id = TU.user_type_id
WHERE C.object_id IN ( OBJECT_ID('dbo.Expected'), OBJECT_ID('dbo.Actual'))
) T
GROUP BY col_ordinal, name, system_type_id, user_type_id, max_length, precision, scale, collation_name, is_nullable
HAVING COUNT(*) = 1
)
EXEC [tSQLt].Fail 'Unexpected/missing column(s)'
Obviously the above could be made more elaborate to return more details of specific differences and itself be moved into a stored proc to facilitate re-use.