Skip to content

Instantly share code, notes, and snippets.

@JerryNixon

JerryNixon/Assert.sql

Last active Mar 23, 2021
Embed
What would you like to do?
An Assert framework for SQL Server

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.

CREATE SCHEMA Assert
GO
CREATE PROC Assert.Contain
@fragment VARCHAR(1000)
, @string VARCHAR(1000)
, @msg VARCHAR(1000) = 'Contain'
AS
BEGIN
IF charindex(@fragment, @string) = 0
EXEC Assert.Fail @fragment, @string, @msg;
END
GO
CREATE PROC Assert.Equal
@expected SQL_VARIANT
, @actual SQL_VARIANT
, @msg VARCHAR(1000) = 'Equal'
AS
BEGIN
IF (@expected != @actual) OR (@expected IS NULL AND @actual IS NULL)
BEGIN
DECLARE @e NVARCHAR(1000) = CONVERT(NVARCHAR(1000), @expected);
DECLARE @a NVARCHAR(1000) = CONVERT(NVARCHAR(1000), @actual);
EXEC Assert.Fail @e, @a, @msg;
END
END
GO
CREATE PROC Assert.Error
@number INT = NULL
, @contains VARCHAR(50) = NULL
, @msg VARCHAR(1000) = 'Error'
AS
BEGIN
DECLARE @error_number INT = error_number();
DECLARE @error_message VARCHAR(1000) = error_message();
IF (@error_number IS NULL)
BEGIN
EXEC Assert.fail @number, 'NULL', 'ErrorNumber';
RETURN
END
EXEC Assert.Equal @number, @error_number, 'Error Number Equals';
EXEC Assert.Contain @contains, @error_message, 'Error Message Contains';
END
GO
CREATE PROC Assert.[Fail]
@expected VARCHAR(1000)
, @actual VARCHAR(1000)
, @message VARCHAR(1000)
, @number INT = 50000
AS
BEGIN
WHILE NOT @@trancount = 0 ROLLBACK;
SET @message = CONCAT(
'Expected<'
, @expected
, '>, Actual<'
, @actual
,'>. '
, @message);
THROW @number, @message, 1;
END
GO
CREATE PROC Assert.NotEqual
@expected SQL_VARIANT
, @actual SQL_VARIANT
, @msg VARCHAR(1000) = 'NotEqual'
AS
BEGIN
IF (@expected = @actual) OR (@expected IS NULL AND @actual IS NULL)
BEGIN
DECLARE @e NVARCHAR(1000) = CONVERT(NVARCHAR(1000), @expected);
DECLARE @a NVARCHAR(1000) = CONVERT(NVARCHAR(1000), @actual);
EXEC Assert.Fail @e, @a, @msg;
END
END
GO
CREATE PROC Assert.NotNull
@actual SQL_VARIANT
, @msg VARCHAR(1000) = 'NotNull'
AS
BEGIN
IF (@actual IS NULL)
EXEC Assert.Fail 'VALUE', 'NULL', @msg;
END
GO
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 'NULL', @a, @msg;
END
END
GO
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
CREATE PROC Assert.Rows
@expected INT
, @msg VARCHAR(1000) = 'Rows'
AS
BEGIN
DECLARE @rows INT = @@rowcount;
IF NOT @rows = @expected
EXEC Assert.Fail @expected, @rows, @msg;
END
GO
CREATE PROC Assert.[Throw]
@caller VARCHAR(50)
, @message VARCHAR(1000)
, @withdate BIT = 0
AS
BEGIN
IF (@withdate = 1)
SET @message = CONCAT(SYSDATETIME(), ' ', @message);
SET @message = CONCAT(@caller, ': ', @message);
THROW 51000, @message, 1;
END
GO
CREATE FUNCTION Assert.Equals
(
@expected SQL_VARIANT
, @actual SQL_VARIANT
, @msg VARCHAR(1000)
)
RETURNS SQL_VARIANT AS
BEGIN
IF (@expected != @actual) OR (@expected IS NULL AND @actual IS NULL)
BEGIN
DECLARE @m NVARCHAR(MAX) = CONCAT('Expected <', CAST(@expected AS NVARCHAR(1000)) ,'>. ', 'Actual<', CAST(@actual AS NVARCHAR(1000)) ,'>. ', COALESCE(@msg, 'Equals. '));
-- errors cannot be raised in a function, but data type mismatches cause errors and pass the message in them.
DECLARE @b BIT = CAST(@m AS BIT);
END
RETURN @actual;
END
GO
PRINT 'Assert Tests.';
-- test Assert.equal
EXEC Assert.equal '1', '1';
-- test Assert.notequal
EXEC Assert.notequal '1', '0';
-- test Assert.[null]
EXEC Assert.[null] NULL;
-- test Assert.notnull
EXEC Assert.notnull '1';
-- test Assert.contain
EXEC Assert.contain '1', '123';
-- test Assert.Equals()
SELECT Assert.Equals('1', '1', NULL)
-- test Assert.Rows
EXEC Assert.Rows 1;
BEGIN TRY
-- test Assert.fail
EXEC Assert.fail 'test', 'test', 'test'
END TRY
BEGIN CATCH
-- test Assert.error
EXEC Assert.error 50000
END CATCH
PRINT 'Pass.';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment