Using Transact-SQL Assertions in Database Unit Tests

  • Article

In a database unit test, a Transact-SQL (T-SQL) test script runs and returns a result. Sometimes, the results are returned as a results set. You can validate results by using test conditions. For example, you can use a test condition to check how many rows were returned in a specific result set or to verify how long a particular test took to run. For more information about test conditions, see Using Test Conditions in Database Unit Tests.

Instead of using test conditions, you can also use T-SQL assertions, which are RAISERROR statements in a T-SQL script. In certain circumstances, you might prefer to use a T-SQL assertion instead of a test condition.

Using T-SQL Assertions

You should consider the following points before you decide to validate data either by using T-SQL assertions or by using test conditions.

  • Performance. It is faster to run a T-SQL assertion on the server than to first move data to a client computer and manipulate it locally.

  • Familiarity with language. You might prefer a particular language based on your current expertise and therefore choose T-SQL assertions or C# or Visual Basic test conditions.

  • Complicated validation. In some instances, you can build more complex test validation more just in C# or Visual Basic and validate your tests on the client.

  • Legacy validation libraries. If you already have code that performs validation, you can use it in a database unit test instead of using test conditions.

Mark Unit Test Methods with the Expected Exception

To mark a database unit test method with expected exceptions, add the following attribute:

<ExpectedSqlException(MessageNumber=nnnnn, Severity=x, MatchFirstError=false, State=y)> _

[ExpectedSqlException(MessageNumber=nnnnn, Severity=x, MatchFirstError=false, State=y)]

Where:

  • nnnnn is the number of the expected message, for example 14025

  • x is the severity of the expected exception

  • y is the state of the expected exception

Any unspecified parameters are ignored. You pass these parameters to the RAISERROR statement in your database code. If you specify MatchFirstError = true, the attribute will match any of the SqlErrors in the exception. The default behavior (MatchFirstError = true) is to only match the first error that occurs.

For an example of how to use expected exceptions and a negative database unit test, see Walkthrough: Creating and Running a Database Unit Test.

The RAISERROR Statement

You can directly use T-SQL assertions on the server by using the RAISERROR statement in your T-SQL script. Its syntax is:

**RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)**

where:

@ErrorMessage is any user-defined error message. You can format this message string similar to the printf_s function.

@ErrorSeverity is a user-defined severity level from 0 – 18.

Note

The values '0' and '10' for the severity level do not cause the database unit test to fail. You can use any other value in the range 0 - 18 to cause the test to fail.

@ErrorState is an arbitrary integer from 1 – 127. You can use this integer to differentiate between occurrences of a single error that is raised at different locations in the code.

For more information, see "RAISERROR (Transact-SQL)" (https://go.microsoft.com/fwlink/?LinkId=66014) in the SQL Server Books Online. An example of using RAISERROR in a database unit test is provided in the topic, How to: Write a Database Unit Test that Runs within the Scope of a Single Transaction.

See Also

Tasks

How to: Open a Database Unit Test to Edit

Concepts

Using Test Conditions in Database Unit Tests

Terminology Overview of Database Edition

Other Resources

Overview of Database Unit Testing

Change History

Date

History

Reason

June 2010

Added a section that describes the attribute that you must put on your unit test methods to specify the expected exceptions to address customer feedback.

Customer feedback.