The Assert Schema is modeled after the Assert namespace/capabilities in C# which is used to create a claim. For example, you could create a claim that two values are equal in C# by using Assert.Equal(expected_value, actual_value)
and if that claim is false, an exception is thrown by the framework. Similarly, this SQL Server implementation of Assert allows you to create claims in TSQL. This type of functionality is handy when testing data or queries. It is also handy inside larger queries to validate sub-steps.
Use Contain to validate a string fragment is inside a larger string.
fragment
: the string you want to findstring
: the string you are searching in[message]
: (optional/default=null)
Syntax
DECLARE @fragment VARCHAR(50) = 'Jerry';
DECLARE @string VARCHAR(50) = 'Jerry Nixon';
EXEC Assert.Contain @fragment, @string;
Result An error is thrown if the fragment is not found.
Use Equal to validate two values are the same.
expected
: the expected valueactual
: the actual valuemessage
: (optional/default=null)
Syntax
DECLARE @expected VARCHAR(50) = 'Jerry';
DECLARE @actual VARCHAR(50) = 'Jerry';
EXEC Assert.Equal @expected, @actual;
Result An error is thrown if the two values are not Equal
Use Equals to validate two values are the same. Because it can be used in a SELECT, it returns ACTUAL.
expected
: the expected valueactual
: the actual valuemessage
: (optional/default=null)
Syntax
SELECT Assert.Equals('Jerry', NAME_COLUMN, 'Name should equal Jerry') AS NAME_COLUMN
FROM USERS
Result An error is thrown if the two values are not Equal. And Actual is returned if the two values are Equal.
Use NotEqual to validate two values are not t6he same.
expected
: the expected valueactual
: the actual valuemessage
: (optional/default=null)
Syntax
DECLARE @expected VARCHAR(50) = 'Jerry';
DECLARE @actual VARCHAR(50) = 'Nixon';
EXEC Assert.NotEqual @expected, @actual;
Result An error is thrown if the two values are Equal.
Use [Null] to validate a value is null. Note that [square brackets] are required since Null is a reserved word.
actual
: the actual value that should be nullmessage
: (optional/default=null)
Syntax
DECLARE @actual VARCHAR(50) = NULL;
EXEC Assert.[Null] @actual;
Result An error is thrown if the value is not null
Use NotNull to validate a value is not null.
actual
: the actual value that should not be nullmessage
: (optional/default=null)
Syntax
DECLARE @actual VARCHAR(50) = 'Nixon';
EXEC Assert.NotNull @actual;
Result An error is thrown if the value is null
Use Rows to validate an operation impacts a certain number of rows.
expected
: the expected number of rows returnedmessage
: (optional/default=null)
Syntax
SELECT * FROM TABLE
DECLARE @expected INT = 10;
EXEC Assert.Rows @expected;
Result An error is thrown if the resulting rows do to equal the expected
Use Fail to manually cause a failure. Note that this is typically used by the other methods, internally.
expected
: the expected value to reportactual
: the actual value to reportmessage
: the custom message to reportnumber
: (optional/default=50000)
Syntax
DECLARE @expected VARCHAR(50) = 'Jerry'
DECLARE @actual VARCHAR(50) = 'Nixon'
DECLARE @message VARCHAR(50) = 'Error Message'
EXEC Assert.Fail @expected, @actual, @message;
Result An error is thrown. Every time.
Use Error to validate an error occurred with expected values.
number
: (optional/default=do not test): the expected error number.contains
: (optional/default=do not test): the expected error message fragment.message
: (optional/default=null)
Syntax
BEGIN TRY
/* YOUR WORK */
END
BEGIN CATCH
DECLARE @number INT = 50000
DECLARE @contains VARCHAR(50) = 'DATA TYPE'
EXEC Assert.Error @number, @contains;
END
Result An error is thrown if the current error context does not meet the criteria
Use PrintLine to simply print a message. Note that this prints with NOWAIT even when SQL server is actively processing.
message
: the string to print/outputwithdate
: (optional/default=true)
Syntax
DECLARE @message VARCHAR(50) = 'Jerry Nixon';
EXEC Assert.PrintLine @message;
Result A string is written to console.