An Assert framework for SQL Server

readme.md

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.

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.

Assert.sql

CREATE SCHEMA assert;

GO

/*
	assert.Contain
	Ensure fragment is inside string.
	@fragment: String to search for
	@string: String to search in
	@msg: (optional) Append custom error message - default: NULL
*/

CREATE PROC assert.Contain
	  @fragment VARCHAR(1000)
	, @string VARCHAR(1000)
	, @msg VARCHAR(1000) = 'Contain'
AS
BEGIN

	IF (@fragment IS NULL OR @string IS NULL)
	BEGIN
		SET @msg = CONCAT('Hint: Check for nulls. ', @msg);
		EXEC assert.Fail @msg, @fragment, @string, 'assert.Contain';
	END

	IF (charindex(@fragment, @string) = 0)
	BEGIN
		EXEC assert.Fail @msg, @fragment, 'NOT FOUND', 'assert.Contain';
	END
		
END;

GO

/*
	assert.Equal
	Ensure two values are equal.
	@expected: Left value to check equality.
	@actual: Right value to check equality.
	@msg: (optional) Append custom error message - default: NULL
*/

CREATE PROC assert.Equal
	  @expected SQL_VARIANT
	, @actual SQL_VARIANT
	, @msg VARCHAR(1000) = 'Equal'
AS
BEGIN

	DECLARE @e NVARCHAR(1000) = CONVERT(NVARCHAR(1000), @expected);
	DECLARE @a NVARCHAR(1000) = CONVERT(NVARCHAR(1000), @actual);

	IF (@expected IS NULL OR @actual IS NULL)
	BEGIN
		SET @msg = CONCAT('Hint: Check for nulls. ', @msg);
		EXEC assert.Fail @msg, @e, @a, 'assert.Equal';
	END

	IF (@expected != @actual)
	BEGIN
		IF (@e = @a)
		BEGIN
			SET @msg = CONCAT('Hint: Check datatypes. ', @msg);
			EXEC assert.Fail @msg, @e, @a, 'assert.Equal';
		END
	ELSE
		EXEC assert.Fail @msg, @e, @a, 'assert.Equal';
	END

END;

GO

/*
	assert.Error
	Ensure latests error values match.
	@expected: (optional) Ensure @error_number matches @expected - default: NULL
	@contains: (optional) Ensure @error_message contains @contains - default: NULL
	@msg: Append custom error message
*/

CREATE PROC assert.ErrInfo
	  @expected INT = NULL
	, @contains VARCHAR(50) = NULL
	, @msg VARCHAR(1000) = 'Error'
AS
BEGIN

	IF (@expected IS NOT NULL)
	BEGIN
		DECLARE @error_number INT = error_number();
		EXEC assert.Equal @expected, @error_number, 'Error Number Equals';
	END

	IF (@contains IS NOT NULL)
	BEGIN
		DECLARE @error_message VARCHAR(1000) = error_message();
		EXEC assert.Contain @contains, @error_message, 'Error Message Contains';
	END

END;

GO

/*
	assert.Fail
	Used to raise an exception, generally only used by assert methods
	@message: error_message()
	@expected: (optional) Builds the Expected<@expected> Actual<> substring 
		Note: if not passed in, the substring is not built.
	@contains: (optional) Builds the Expected<> Actual<@actual> substring 
		Note: if not passed in, the substring is not built.
	@caller: (optional) Prepends caller to message - default: 'assert.Fail'
	@number: (optional) error_number() - default: 50000
*/

CREATE PROC assert.Fail
	  @message VARCHAR(1000)
  	, @expected VARCHAR(1000) = NULL
	, @actual VARCHAR(1000) = NULL
	, @caller VARCHAR(1000) = 'assert.Fail'
	, @number INT = 50000
AS 
BEGIN

	IF NOT (@expected = 'assert.Fail' OR @actual = 'assert.Fail')
	BEGIN
		SET @message = CONCAT(
			'[[ Expected<', COALESCE(@expected, 'NULL'), '>, ',
			'Actual<', COALESCE(@actual, 'NULL'),'>. ',
			@message, ' ]]');
	END

	EXEC assert.Throw @number = @number, @caller = @caller, @message = @message, @withdate = 0;

END;

GO

/*
	assert.NotEqual
	Ensure two values are not equal.
	@expected: Left value to check equality.
	@actual: Right value to check equality.
	@msg: (optional) Append custom error message - default: NULL
*/

CREATE PROC assert.NotEqual
	  @expected SQL_VARIANT
	, @actual SQL_VARIANT
	, @msg VARCHAR(1000) = 'NotEqual'
AS
BEGIN

	IF (@expected IS NULL)
	BEGIN
		RETURN;
	END

	IF (@actual IS NULL)
	BEGIN
		RETURN;
	END

	IF (@expected = @actual)
	BEGIN
		DECLARE @e NVARCHAR(1000) = CONCAT('NOT ', CONVERT(NVARCHAR(1000), @expected));
		DECLARE @a NVARCHAR(1000) = CONVERT(NVARCHAR(1000), @actual);
		EXEC assert.Fail @msg, @e, @a, 'assert.NotEqual';
	END

END;

GO

/*
	assert.NotNull
	Ensure value is not NULL.
	@msg: (optional) Append custom error message - default: NULL
*/

CREATE PROC assert.NotNull
	  @actual SQL_VARIANT
	, @msg VARCHAR(1000) = 'NotNull'
AS
BEGIN

	IF (@actual IS NULL)
	BEGIN
		EXEC assert.Fail @msg, 'NOT NULL', 'NULL', 'assert.NotNull';
	END

END;

GO

/*
	assert.Null
	Ensure value is NULL.
	@msg: (optional) Append custom error message - default: NULL
*/

CREATE PROC assert.[Null]
	  @actual SQL_VARIANT
	, @msg VARCHAR(1000) = 'Null'
AS
BEGIN

	IF (@actual IS NOT NULL)
	BEGIN
		DECLARE @a NVARCHAR(1000) = CONVERT(NVARCHAR(1000), @actual);
		EXEC assert.Fail @msg, 'NULL', @a, 'assert.Null';
	END

END;

GO

/*
	assert.PrintLine
	Prints line, even when in a loop (which is the special part)
	@message: string to print
	@withdate: (optional) prepends datetime to message - default: 1
*/

CREATE PROCEDURE assert.PrintLine
	  @message VARCHAR(1000)
	, @withdate BIT = 1
AS
BEGIN
	IF (@withdate = 1)
	BEGIN
		DECLARE @PRINT VARCHAR(1000) = CONCAT(SYSDATETIME(), ' ', @message); 
		RAISERROR (@PRINT, 0, 1) WITH NOWAIT;
	END
	ELSE
	BEGIN
		RAISERROR (@message, 0, 1) WITH NOWAIT;
	END
END;

GO

/*
	assert.Throw
	Throws new exception with additional, optional information
	@caller: prepends caller to message
	@message: string to print
	@withdate: (optional) prepends datetime to message - default: 1
	@number: (optional) the error number to throw - default: 51000
*/

CREATE PROC assert.[Throw]
	  @caller VARCHAR(50)
	, @message VARCHAR(1000)
	, @withdate BIT = 0
	, @number INT = 51000
AS
BEGIN

	IF (@withdate = 1)
	BEGIN
		SET @message = CONCAT(SYSDATETIME(), ' ', @message); 
	END
	SET @message = CONCAT(@caller, ': ', @message);
	DECLARE @print VARCHAR(1500) = CONCAT('#', @number, ' ', @message);
	EXEC assert.PrintLine @print, 0;
	THROW @number, @message, 1;

END;

GO

CREATE FUNCTION assert.ActualRows
(@schema_name VARCHAR(100), @table_name VARCHAR(100)) 
RETURNS BIGINT AS
BEGIN

	DECLARE @actual BIGINT = -1

	SELECT @actual = MAX(p.rows) 
	FROM sys.tables AS t
	JOIN sys.schemas AS s 
		ON t.schema_id = s.schema_id
	JOIN sys.indexes AS i 
		ON t.OBJECT_ID = i.object_id
	JOIN sys.partitions AS p 
		ON i.object_id = p.OBJECT_ID 
		AND i.index_id = p.index_id
	WHERE s.name = @schema_name
		AND t.name = @table_name
	GROUP BY t.Name, s.Name

	RETURN @actual

END;

GO

CREATE PROC assert.[Rows]
	  @schema_name VARCHAR(100)
	, @table_name VARCHAR(100)
	, @expected BIGINT
	, @msg VARCHAR(1000) = NULL
AS
BEGIN

	DECLARE @actual BIGINT;
	SELECT @actual = assert.ActualRows(@schema_name, @table_name);

	DECLARE @a VARCHAR(1000) = @actual;
	DECLARE @e VARCHAR(1000) = @expected;
	SET @msg = CONCAT(@schema_name, '.', @table_name, ' row count should match. ', @msg);
	IF NOT (@expected = @actual)
	BEGIN
		EXEC assert.Fail @msg, @e, @a, 'assert.Rows';
	END

END;

GO

CREATE PROC assert.NotRows
	  @schema_name VARCHAR(100)
	, @table_name VARCHAR(100)
	, @expected BIGINT
	, @msg VARCHAR(1000) = NULL
AS
BEGIN

	DECLARE @actual BIGINT;
	SELECT @actual = assert.ActualRows(@schema_name, @table_name);

	DECLARE @a VARCHAR(1000) = @actual;
	DECLARE @e VARCHAR(1000) = CONCAT('NOT ', @expected);
	SET @msg = CONCAT(@schema_name, '.', @table_name, ' row count should not match.', @msg);
	IF (@expected = @actual)
	BEGIN
		EXEC assert.Fail @msg, @e, @a, 'assert.NotRows';
	END

END;

GO

CREATE PROC assert.HasNoRows
	  @schema_name VARCHAR(100)
	, @table_name VARCHAR(100)
	, @msg VARCHAR(1000) = NULL
AS
BEGIN

	DECLARE @actual BIGINT;
	SELECT @actual = assert.ActualRows(@schema_name, @table_name);

	SET @msg = CONCAT(@schema_name, '.', @table_name, ' should have no rows.', @msg);
	IF (0 != @actual)
	BEGIN
		DECLARE @a VARCHAR(1000) = @actual;
		EXEC assert.Fail @msg, '0', @a, 'assert.HasNoRows';
	END

END;

GO

CREATE PROC assert.HasRows
	  @schema_name VARCHAR(100)
	, @table_name VARCHAR(100)
	, @msg VARCHAR(1000) = NULL
AS
BEGIN

	DECLARE @actual BIGINT;
	SELECT @actual = assert.ActualRows(@schema_name, @table_name);

	SET @msg = CONCAT(@schema_name, '.', @table_name, ' should have some rows.', @msg);
	IF (0 = @actual)
	BEGIN
		DECLARE @a VARCHAR(1000) = @actual;
		EXEC assert.Fail @msg, 'NOT 0', @a, 'assert.HasRows';
	END

END;

TestAssert.sql

CREATE PROC tests.ASSERT_Contain AS

	EXEC assert.Contain 'Jerry', 'Jerry Nixon', 'Unit Test/CONTAIN ok';

	BEGIN TRY
		EXEC assert.Contain 'Nischay', 'Jerry Nixon', 'Unit Test/CONTAIN mismatch';
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @contains = 'Unit Test/CONTAIN mismatch';
	END CATCH;

	BEGIN TRY
		EXEC assert.Contain NULL, 'Jerry Nixon', 'Unit Test/CONTAIN null, CHAR mismatch';
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @contains = 'Unit Test/CONTAIN null, CHAR mismatch';
	END CATCH;

	BEGIN TRY
		EXEC assert.Contain 'Nischay', NULL, 'Unit Test/CONTAIN CHAR, NULL mismatch';
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @contains = 'Unit Test/CONTAIN CHAR, NULL mismatch';
	END CATCH;

	BEGIN TRY
		EXEC assert.Contain NULL, NULL, 'Unit Test/CONTAIN NULL, NULL mismatch';
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @contains = 'Unit Test/CONTAIN NULL, NULL mismatch';
	END CATCH;

GO

CREATE PROC tests.ASSERT_Equal AS

	EXEC assert.Equal 1, 1, 'Unit Test/Equal INT INT ok';

	EXEC assert.Equal '1', '1', 'Unit Test/Equal CHAR CHAR ok';

	DECLARE @d DATETIME = CAST('1/1/2020' AS DATETIME);
	EXEC assert.Equal @d, @d, 'Unit Test/EQUAL DATE DATE ok';

	BEGIN TRY
		EXEC assert.Equal '2', '1', 'Unit Test/EQUAL CHAR CHAR mismatch';
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @contains = 'Unit Test/EQUAL CHAR CHAR mismatch';
	END CATCH;

	BEGIN TRY
		EXEC assert.Equal 2, 1, 'Unit Test/EQUAL INT INT mismatch';
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @contains = 'Unit Test/EQUAL INT INT mismatch';
	END CATCH;

	BEGIN TRY
		EXEC assert.Equal 1, '1', 'Unit Test/EQUAL INT CHAR mismatch';
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @contains = 'Unit Test/EQUAL INT CHAR mismatch';
	END CATCH;

	BEGIN TRY
		EXEC assert.Equal NULL, '1', 'Unit Test/EQUAL NULL CHAR mismatch';
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @contains = 'Unit Test/EQUAL NULL CHAR mismatch';
	END CATCH;

	BEGIN TRY
		EXEC assert.Equal '1', NULL, 'Unit Test/EQUAL CHAR NULL mismatch';
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @contains = 'Unit Test/EQUAL CHAR NULL mismatch';
	END CATCH;

	BEGIN TRY
		EXEC assert.Equal NULL, NULL, 'Unit Test/EQUAL NULL NULL mismatch';
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @contains = 'Unit Test/EQUAL NULL NULL mismatch';
	END CATCH;

GO

CREATE PROC tests.ASSERT_Error AS

	BEGIN TRY
		THROW 50001, 'Jerry Nixon', 1;
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @expected = 50001, @contains = 'Jerry Nixon', @msg = 'UNIT TEST/ERROR @expected, @contains ok';
	END CATCH;

	BEGIN TRY
		THROW 50001, 'Jerry Nixon', 1;
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @expected = 50001, @msg = 'UNIT TEST/ERROR @expected ok';
	END CATCH;

	BEGIN TRY
		THROW 50001, 'Jerry Nixon', 1;
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @contains = 'Jerry Nixon', @msg = 'UNIT TEST/ERROR @contains ok';
	END CATCH;

GO

CREATE PROC tests.ASSERT_NotEqual AS

	EXEC assert.NotEqual 1, 2, 'Unit Test/NotEqual INT INT ok';

	EXEC assert.NotEqual '1', '2', 'Unit Test/NotEqual CHAR CHAR ok';

	DECLARE @d DATETIME = CAST('1/1/2020' AS DATETIME);
	DECLARE @d2 DATETIME = CAST('1/1/2021' AS DATETIME);
	EXEC assert.NotEqual @d, @d2, 'Unit Test/NotEqual DATE DATE ok';

	EXEC assert.NotEqual 1, '1', 'Unit Test/NotEqual INT CHAR ok';

	EXEC assert.NotEqual NULL, '1', 'Unit Test/NotEqual NULL CHAR ok';

	EXEC assert.NotEqual '1', NULL, 'Unit Test/NotEqual CHAR NULL ok';

	EXEC assert.NotEqual NULL, NULL, 'Unit Test/NotEqual NULL NULL ok';

	BEGIN TRY
		EXEC assert.NotEqual '1', '1', 'Unit Test/NotEqual CHAR CHAR mismatch';
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @contains = 'Unit Test/NotEqual CHAR CHAR mismatch';
	END CATCH;

	BEGIN TRY
		EXEC assert.NotEqual 1, 1, 'Unit Test/NotEqual INT INT mismatch';
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @contains = 'Unit Test/NotEqual INT INT mismatch';
	END CATCH;

GO

CREATE PROC tests.ASSERT_NotNull AS

	EXEC assert.NotNull 1, 'Unit Test/NotNull INT ok';

	EXEC assert.NotNull '1', 'Unit Test/NotNull CHAR ok';

	DECLARE @d DATETIME = CAST('1/1/2020' AS DATETIME);
	EXEC assert.NotNull @d, 'Unit Test/NotNull DATE ok';

	BEGIN TRY
		EXEC assert.NotNull NULL, 'Unit Test/NotNull NULL mismatch';
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @contains = 'Unit Test/NotNull NULL mismatch';
	END CATCH;

GO

CREATE PROC tests.ASSERT_Null AS

	EXEC assert.[Null] NULL, 'Unit Test/Null NULL ok';

	BEGIN TRY
		EXEC assert.[Null] '1', 'Unit Test/Null CHAR mismatch';
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @contains = 'Unit Test/Null CHAR mismatch';
	END CATCH;

	BEGIN TRY
		EXEC assert.[Null] 1, 'Unit Test/Null INT mismatch';
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @contains = 'Unit Test/Null INT mismatch';
	END CATCH;

GO

CREATE PROC tests.ASSERT_Fail AS

	BEGIN TRY
		EXEC assert.Fail 'Unit Test/Fail @message ok';
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @contains = 'Unit Test/Fail @message ok';
	END CATCH;

	BEGIN TRY
		EXEC assert.Fail 'Unit Test/Fail @expected, @actual ok', @expected = 1, @actual = 2;
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @contains = 'Unit Test/Fail @expected, @actual ok';
	END CATCH;

	BEGIN TRY
		EXEC assert.Fail 'Unit Test/Fail @caller ok', @caller = 'assert.Jerry';
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @contains = 'assert.Jerry';
	END CATCH;

	BEGIN TRY
		EXEC assert.Fail 'Unit Test/Fail @number ok', @number = 51123;
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @expected = 51123;
	END CATCH;

GO

CREATE PROC tests.ASSERT_PrintLine AS

	EXEC assert.PrintLine 'Jerry Nixon', 1;
	EXEC assert.PrintLine 'Jerry Nixon', 0;

GO

CREATE PROC tests.ASSERT_Throw AS

	BEGIN TRY
		EXEC assert.Throw 'assert.Throw', 'Unit Test/Throw @caller @message ok';
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @contains = 'Unit Test/Throw @caller @message ok';
	END CATCH;

	BEGIN TRY
		EXEC assert.Throw 'assert.Throw', 'Unit Test/Throw @caller @message @withdate ok', 1;
	END TRY
	BEGIN CATCH
		DECLARE @year VARCHAR(4) = DATEPART(year, GETDATE());
		EXEC assert.ErrInfo @contains = @year;
	END CATCH;

	BEGIN TRY
		EXEC assert.Throw 'assert.Throw', 'Unit Test/Throw @caller @message @withdate @number ok', 0, 51234;
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @expected = 51234;
	END CATCH;

GO

CREATE PROC tests.ASSERT_Rows AS

	BEGIN TRANSACTION;
	SET NOCOUNT ON;

	CREATE TABLE assert.AssertRowsTest (Id INT);
	INSERT INTO assert.AssertRowsTest (Id) VALUES (1), (2);

	EXEC assert.Rows 'assert', 'AssertRowsTest', 2, 'Unit Test/Rows ok';

	BEGIN TRY
		EXEC assert.Rows 'assert', 'AssertRowsTest', 1, 'Unit Test/Rows mismatch';
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @contains = 'Unit Test/Rows mismatch';
	END CATCH;

	ROLLBACK;

GO

CREATE PROC tests.ASSERT_NotRows AS

	BEGIN TRANSACTION;
	SET NOCOUNT ON;

	DROP TABLE IF EXISTS assert.AssertNotRowsTest;
	CREATE TABLE assert.AssertNotRowsTest (Id INT);
	INSERT INTO assert.AssertNotRowsTest (Id) VALUES (1), (2);

	EXEC assert.NotRows 'assert', 'AssertNotRowsTest', 1, 'Unit Test/NotRows ok';

	BEGIN TRY
		EXEC assert.NotRows 'assert', 'AssertNotRowsTest', 2, 'Unit Test/NotRows mismatch';
	END TRY
	BEGIN CATCH
		EXEC assert.ErrInfo @contains = 'Unit Test/NotRows mismatch';
	END CATCH;

	ROLLBACK;