An Assert Schema for SQL Server

In C#, we use Assert to make a claim and to do a few utility operations. For example, we use Assert.Equal(one, two) to claim that one and two have the same values. If this is not true, Assert.Equal() throws an exception that is typically used by Unit Test runners to highlight failing tests.

But in SQL Server, there is no Unit Test framework or runner. That doesn’t mean we can’t have similar behaviors to Assert in TSQL. We just have to write them ourselves. To that end, I have created a starter set of scripts to introduce Assert to SQL Server for database developers.

You can find it here: An Assert Schema for SQL Server (github.com)

 

EXEC Assert.Contain fragment, string, [message];

Use Contain to validate a string fragment is inside a larger string.

1. fragment: the string you want to find
2. string: the string you are searching in
3. [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.

---

EXEC Assert.Equal expected, actual, [message];

Use Equal to validate two values are the same.

1. expected: the expected value
2. actual: the actual value
3. message: (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

---

SELECT Assert.Equals(expected, actual, message);

Use Equals to validate two values are the same. Because it can be used in a SELECT, it returns ACTUAL.

1. expected: the expected value
2. actual: the actual value
3. message: (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.

---

EXEC Assert.NotEqual expected, actual, [message];

Use NotEqual to validate two values are not t6he same.

1. expected: the expected value
2. actual: the actual value
3. message: (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.

---

EXEC Assert.[Null] actual, [message];

Use [Null] to validate a value is null. Note that [square brackets] are required since Null is a reserved word.

1. actual: the actual value that should be null
2. message: (optional/default=null)

Syntax

DECLARE @actual VARCHAR(50) = NULL;
EXEC Assert.[Null] @actual;

Result
An error is thrown if the value is not null

---

EXEC Assert.NotNull actual, [message];

Use NotNull to validate a value is not null.

1. actual: the actual value that should not be null
2. message: (optional/default=null)

Syntax

DECLARE @actual VARCHAR(50) = 'Nixon';
EXEC Assert.NotNull @actual;

Result
An error is thrown if the value is null

---

EXEC Assert.Rows expected, [message];

Use Rows to validate an operation impacts a certain number of rows.

1. expected: the expected number of rows returned
2. message: (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

---

EXEC Assert.Fail expected, actual, message, [number];

Use Fail to manually cause a failure. Note that this is typically used by the other methods, internally.

1. expected: the expected value to report
2. actual: the actual value to report
3. message: the custom message to report
4. number: (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.

---

EXEC Assert.Error [number], [contains], [message];

Use Error to validate an error occurred with expected values.

1. number: (optional/default=do not test): the expected error number.
2. contains: (optional/default=do not test): the expected error message fragment.
3. 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

---

EXEC Assert.[PrintLine] message, [withdate];

Use PrintLine to simply print a message. Note that this prints with NOWAIT even when SQL server is actively processing.

1. message: the string to print/output
2. withdate: (optional/default=true)

Syntax

DECLARE @message VARCHAR(50) = 'Jerry Nixon';
EXEC Assert.PrintLine @message;

Result
A string is written to console.