|Important||This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here. ArchiveDisclaimer|
Using T-SQL Assertions in Database Unit Tests
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.
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.
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)
@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.
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)" (http://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, .