TSQLMacro
macro expansion for sql server

TSQLAssert: Assertion Framework for SQL Server

TSQLAssert is an assertion framework built on top of TSQLMacro. It is intended to provide debug-time assertion failures similar to assertions in languages like C++ -- with an additional logging component not found in those languages. TSQLAssert can be used only within stored procedures and triggers -- unfortunately, user-defined functions and views do not support many of the keywords that allow it to work.

Note that this document is current as of TSQLAssert version 0.5 (prebeta-1). It will be updated as the framework matures.

What is an assertion?

An assertion is a declaration that, at a certain point in the runtime of a routine, a variable or object should be in a certain state. For instance, a developer might know that within a given IF block, a variable should have a value greater than 1. If the variable does not have that value, this constitutes an assertion failure.

Assertion failures in other languages can cause immediate termination of the entire program, or only immediate termination of the active routine. In TSQL, only the latter is possible. TSQLAssert assertion failures roll back any active transactions, raise an error of severity 16, and return control from the procedure or trigger.

What assertions are available in TSQLAssert?

As of the current version of TSQLAssert, three assertion types are supported: IS_IN, IS_BETWEEN, and IS_TRUE.

Future versions of TSQLAssert will support more assertion types. Please e-mail suggestions to the contact address at the bottom of this page.

How is TSQLAssert installed?

First, download the TSQLAssert install package from the TSQLMacro download page.

TSQLAssert has two install scripts: A server install script and a database install script. Both must be run in order to get the full functionality.

The server install script creates a database called 'TSQLAssert', and one stored procedure in the master database for each assertion (prefixed with 'sp_Assert'.) The TSQLAssert database currently contains a generic assertion logging procedure and a table in which to log assertion failures. The stored procedures in master contain the assertion logic itself. They are created in master such that they can be available for every database in the system.

The second, database-specific install script, can only be run in databases in which TSQLMacro has been previously installed. This install script creates TSQLMacro inline macros that wrap the TSQLAssert functionality and allow the transaction rollback and return control functionality. This script must be run in every database in which TSQLAssert is to be used within routines.

How is TSQLAssert used?

TSQLAssert macros are embedded within routines using the same syntax, and following the same rules, as other TSQLMacro inline macros. Please read the TSQLMacro tour for more information if you haven't already. TSQLAssert macros are created within a MacroClass called "DEBUG".

TSQLAssert currently includes the following inline macro definitions:

ASSERT_IS_IN(#X#, #IN_CLAUSE#)

ASSERT_IS_BETWEEN(#X#, #Y1#, #Y2#)

ASSERT_IS_TRUE(#X#)
Assertion Types

ASSERT_IS_IN takes two parameters: #X# is a scalar value -- including any valid string or local variable. Note that this parameter will be treated as a scalar and will not be evaluated. The second parameter, #IN_CLAUSE#, is any valid string (including a comma-delimited list), local variable/scalar value, or subquery that can go into an IN clause. The value of this parameter can, therefore, be evaluated at runtime.

For instance, assume that you're a developer writing a stored procedure to update the number of units in stock for products that your company sells:

CREATE PROCEDURE UpdateProductStockUnits
	@ProductID INT,
	@UnitsInStock INT
AS
BEGIN
	SET NOCOUNT ON

	UPDATE Products
	SET UnitsInStock = @UnitsInStock
	WHERE ProductID = @ProductID
END

A basic assumption made by the developer when creating this stored procedure is that the value for @ProductID is actually a valid ProductID in the Products table. If an application is passing in values that are not valid, no error will result -- and the bug may remain uncaught and deployed to a production environment where it will cause damage: incorrect out-of-stock messages returned to customers.

To combat this issue, an IS_IN assertion can be inserted:

CREATE PROCEDURE UpdateProductStockUnits
	@ProductID INT,
	@UnitsInStock INT
AS
BEGIN
	SET NOCOUNT ON

	/*#ASSERT_IS_IN(@ProductID, 'SELECT ProductID FROM Products')#*/

	UPDATE Products
	SET UnitsInStock = @UnitsInStock
	WHERE ProductID = @ProductID
END

This assertion tells TSQLAssert that the passed-in value for @ProductID must be one of the values in the ProductID column of the Products table. Once this assertion is enabled (see below), this stored procedure will return an error and stop running if an invalid @ProductID value is encountered -- and the developer will know that there's a problem.

ASSERT_IS_BETWEEN takes three parameters: #X# is a scalar value, and much like the same parameter for the IS_IN assertion, can accept any scalar or local variable. #Y1# and #Y2# define bounds that the developer is asserting #X# must fall between. These bounds can be defined by any scalar variable, literal (e.g. a string or number), or scalar subquery. Note that this assertion uses the BETWEEN operator internally -- so boundry conditions are inclusive (e.g., 1 is between 1 and 10.)

Going back to the UpdateProductStockUnits example, the developer could use ASSERT_IS_BETWEEN if, for instance, some knowledge existed about the amount of stock that the company could possibly keep for a given product. A good assumption for a lower bound is 0 -- it's impossible to have a negative amount of stock (unless an exec has decided to set up a complex embezzling scheme, in which case TSQLAssert will catch it!) We'll also assume, for the sake of this exercise, that the Products table has an integer column called MaximumUnitStorage that contains a value representing the maximum number of units of that product that can be stored at any given time in the warehouse.

Given those assumptions, the UpdateProductStockUnits stored procedure could be rewritten as follows:

CREATE PROCEDURE UpdateProductStockUnits
	@ProductID INT,
	@UnitsInStock INT
AS
BEGIN
	SET NOCOUNT ON

	/*#IFDEF(DEBUG)
		--#ASSERT_IS_IN(@ProductID, 'SELECT ProductID FROM Products')#

		DECLARE @sql VARCHAR(200)
		SET @sql = 
			'SELECT MaximumUnitStorage ' +
			'FROM Products ' + 
			'WHERE ProductID = ' + CONVERT(VARCHAR, @ProductID)

		--#ASSERT_IS_BETWEEN(@UnitsInStock, 0, @sql)#
	#ENDIF#*/

	UPDATE Products
	SET UnitsInStock = @UnitsInStock
	WHERE ProductID = @ProductID
END

There are several things to note here. First of all, the macros have been wrapped in a multi-line macro for the MacroClass "DEBUG" (which the TSQLAssert macros are a member of) -- and the inline macros were converted into the embedded inline macro format. This was done such that the SELECT statement for the upper-bound (MaxiumUnitStorage) could be formatted before passing it to the assertion -- this may or may not be necessary in all cases, but is probably a good idea when dealing with dynamic SQL, in order to maintain better readability. Because the variable was declared within a DEBUG block, it will only be available when the DEBUG MacroClass is enabled -- and we assume that's only in debug and quality assurance environments -- so this variable will not even exist at runtime in production environments.

This also brings up a very important point about working with debug-enabled macros. Although the TSQLAssert macros, unlike macros in some other languages, cannot affect data, multiline TSQLMacro macros CAN include data manipulation code. It's important to remember that data manipulation relied upon by non-debug blocks should never be done within debug blocks -- or else rolling the code to production will cause it to break.

Another note on this example: If there truly were a MaximumUnitStorage column available in a real-world products table, the value of the UnitsInStock column should be enforced by a CHECK constraint. This does not negate the need for the assertion; we would still like to be alerted prior to a constraint violation that there is a problem, and the assertion tells us exactly where and why a violation would have occurred. But it's worth noting that CHECK constraints are grossly underused and can lead to vastly better data quality.

ASSERT_IS_TRUE takes only a single parameter: #X# is any string literal or local string variable that will evaluate to a predicate. Examples include '1 = 1', ''A' IN ('A', 'B', 'C')', and 'EXISTS (SELECT * FROM Tbl)'. This assertion is very flexible, but requires the most pre-formulation of dynamic SQL before calling it.

Returning agian to the UpdateProductStockUnits example, we could make a very contrived assumption: The application should only update the units in stock if the value is different from the value currently in the table. Note that this may not be a realistic assumption, but it displays the utility of ASSERT_IS_TRUE quite nicely!

Given that assumption, the UpdateProductStockUnits stored procedure could be re-written one final time as follows:

CREATE PROCEDURE UpdateProductStockUnits
	@ProductID INT,
	@UnitsInStock INT
AS
BEGIN
	SET NOCOUNT ON

	/*#IFDEF(DEBUG)
		--#ASSERT_IS_IN(@ProductID, 'SELECT ProductID FROM Products')#

		DECLARE @sql VARCHAR(200)
		SET @sql = 
			'SELECT MaximumUnitStorage ' +
			'FROM Products ' + 
			'WHERE ProductID = ' + CONVERT(VARCHAR, @ProductID)

		--#ASSERT_IS_BETWEEN(@UnitsInStock, 0, @sql)#

		DECLARE @sql2 VARCHAR(200)
		SET @sql2 = 
			'(SELECT UnitsInStock ' +
			'FROM Products ' +
			'WHERE ProductID = ' + CONVERT(VARCHAR, @ProductID) +
			') <> ' + CONVERT(VARCHAR, @UnitsInStock)

		--#ASSERT_IS_TRUE(@sql2)#
	#ENDIF#*/

	UPDATE Products
	SET UnitsInStock = @UnitsInStock
	WHERE ProductID = @ProductID
END

So what are we asserting? The number of UnitsInStock in the Products table before the update must not be the same as the value we're about to update the table with, or the assertion will fail.

Enabling The Macros

Before TSQLAssert macros start working, they must be activated and the routine in which they're embedded must be preprocessed. For more information on macro activation and preprocessing, please read the TSQLMacro tour.

The TSQLAssert macros and the DEBUG MacroClass are activated by default when the TSQLAssert macros are installed in a database.

To preprocess the UpdateProductStockUnits stored procedure, the following TSQL would be used:

EXEC MacroProcess
	@RoutineName = 'UpdateProductStockUnits'

Once the routine is processed, the assertions are active -- they will be considered when the routine is executed.

Assertion Failures

An assertion failure occurs whenever the assumption made by the assertion is not true. For instance, the IS_IN assertion made in the UpdateProductStockUnits will fail if an invalid ProductID is passed in. The IS_BETWEEN assertion will fail if the number of units passed in is negative or greater than the maximum allowed. And the IS_TRUE assertion will fail if the number of units passed in is equal to the number already in the table.

When an assertion failure occurs, the routine will immediately terminate -- so the rest of the assertions and the rest of the SQL in the routine will not be processed. An error will be raised with severity 16, the error will be logged along with various helper data to the AssertionLog table in the TSQLAssert database, and an error message will be returned.

For instance, assume that the database has products with IDs between 1 and 100, and the following TSQL is executed:

EXEC UpdateProductStockUnits
	@ProductID = 1001,
	@UnitsInStock = -1

Since 1001 is not a valid ProductID, an assertion error similar to the following will be returned:

Server: Msg 50000, Level 16, State 1, Procedure UpdateProductStockUnits, Line 9
Assertion failure -- ID 1 -- (IsIn) Arguments: (1001) (SELECT ProductID FROM Products) 

Some notes on this: The ID returned is the value of the AssertId column in the AssertionLog table that was allocated for this assertion. The value in the first set of parenthesis is the type of assertion that failed. The value in the second and third set of parenthesis are the arguments passed to the assertion. There can be between one and three sets of parenthesis for arguments -- The IS_TRUE assertion has only one parameter, whereas the IS_BETWEEN assertion has three.

When Should TSQLAssert be Used?

TSQLAssert is designed to be used in development and quality assurance environments only. It is recommended, for performance reasons, that assertions not be enabled in production environments. Assertions can add a large amount of overhead to production code due to their use of subqueries -- this can greatly slow down an application. They can also raise errors that application code may not be designed to properly deal with. In a development environment, this is a good thing -- developers should be alerted immediately when a failure occurs. But in a production environment, it might be wiser for the user interface to give no indication of any kind of problem.

It's recommended that the DEBUG macro class be disabled -- or not created at all -- in production environments, and that the preprocessor be run on all routines to ensure that no assertion or other debug macros are enabled.

Assertions can be used to catch a variety of errors and problems. The most common of these are argument exceptions (such as the invalid ProductId passed in to the UpdateProductStockUnits stored procedure) and logical exceptions -- such as when a developer expects that at a certain point in the execution of a routine, a given variable should have a certain value. These types of problems should also be dealt with using procedural constructs such as IF blocks -- but they should be asserted in development environments so that the code will be as robust as possible.

For more general information on assertions, the following Wikipedia article presents a good introduction:

http://en.wikipedia.org/wiki/Assertion_%28comp uting%29

How is TSQLAssert de-installed?

De-installation is not supported in the current version of TSQLAssert. Look for this feature in the next version.

Conclusion

TSQLAssert provides a means by which developers can quickly and easily create debug assertions within SQL Server stored procedures and triggers. These assertions can assist in more easily identifying certain classes of bugs, and generally lead to more robust code.

Future TSQLAssert Features

Various features are planned for future versions of TSQLAssert. These include:

If you have any feature suggestions, comments, or other feedback about TSQLAssert, please e-mail the contact address at the bottom of this page!


adam machanic
contact: amachanic@datamanipulation.net