Is there a way to assert a "not" condition using tSQLt?

Question

I am new to using tSQLt and struggling a bit with the available assert functions. The tSQLt.AssertEmptyTable method is great, but how do you apply a "not" condition to this, i.e. I want to assert that a table contains data?

Answer 1

That depends on what you actually want to test. Do you want to validate the content of the rows and columns in that table or just that it has one or more rows?

If the former, then tSQLt.AssertEqualsTable will allow you to compare the contents of one table (e.g. a #expected table populated with the values you are expecting) with the table under test EXEC tSQLt.tSQLt.AssertEqualsTable '#expected', 'my_table';. One useful feature of this assertion is that only the columns in #expected are validated. So if #expected has ten columns but my_table as twelve, only the contents of those ten columns are checked, the other two will be ignored by this assertion. This can be useful, for example, when those two columns are auto-populated and so harder to test e.g. an IDENTITY column and a GETDATE() default. Obviously, if #expected has columns that do not exist on my_table the test will fail anyway.

If you just want to check that there is any data at all in the table you can do something like IF NOT (SELECT COUNT(*) FROM my_table) > 0 EXEC tSQLt.Fail 'my_table contains no data'