utTSQL

utTSQL is a unit testing framework for Microsoft SQL Server Transact-SQL using stored procedures.

utTSQL is an Open Source project, using the GNU Lesser General Public License (LGPL) .
Downloads are located on the Source Forge project page: http://sourceforge.net/projects/uttsql/
The utTSQL home page is located here: http://uttsql.sourceforge.net/

Alternatives

utTSQL not quite right for you? There are a couple of alternatives to try:

• Open source, Henrik Ekelund has written 'tsqlunit' a open source (LGPL) testing framework for SQL-Server located on Source Forge here: http://tsqlunit.sourceforge.net/

• Closed source, Microsoft Studio Team Edition for Database Professionals (which is an add on for Visual Studio Team Suite Edition, although it already ships with Team Suite Edition 2008).

Installation

To install utTSQL to an existing database depack/unzip the the source files, maintaining the directory structure, to a directory on the database server. Make sure that the database has permissions to access the directory.

1. Open a command prompt and navigate to the directory 'utTSQL' which contains a batch file called '__InstallScript.bat' (NOTE: it starts with double underscore).

2. Execute this file followed by the name of the database to install to, the database name cannot contain any spaces, e.g. __InstallScript.bat DEV-05-TransactUnitTester

3. You will then be informed of database you are to installing to and asked if you wish to proceed:

4. 
   The installer will run a selection of sql script files against the selected database, log files are generated for each file and placed in the 'utTransactSQL\utTSQL\Logs' directory.

5. Once happy that these details are correct then press any key to continue the installation (if you cannot find the Any key or your keyboard does not have one then the Space Bar will suffice).

6. If not the press the Control key and 'C' at the same time to cancel (Ctrl+C as it is also known) and return to the command prompt.

7. All being well the installation will be completed in a few seconds and you will be presented with:

8. 
   

   If any errors occurred during installation they will be recorded in the 'Logs' directory.

9. You can now close the command prompt.

If you have SQL Server Management Studio open you can view that utTSQL installs several tables, stored procedures and a stored function. Before and After:

utTSQL is installed to its own schema (nothing is placed outside in the DBO schema), there are four schemas used in total:

Self Testing

Once utTSQL has installed you should now run the self tests to check that it is working correctly. This is can be done from the SQL Query window of SQL Server Management Studio by typing the following and pressing F5 to execute the command (remember to select the correct database in the 'Available Databases' drop down menu):

• EXECUTE utTSQL.SelfTest;

In the Message window the following will be displayed:

-------------------------------------------------------------------
-
- Starting utTSQL self testing.
-
- NOTE: Due to the nature of these tests failures will be displayed
-     (they are expect to fail).
-

Followed by the current test package/test case running and any failed assertions. As the NOTE mentions there will be quite a few failures as the tests have to check whether the assertions actually fail as expected.

When the self test is complete, which should only take a couple of seconds, you will be presented with the conclusion of the self test (you will have to scroll to the bottom of the Message window to see it):

-------------------------------------------------------------------
-
- Finished utTSQL self test.
-
- Total Assertions Correct: 300
- Total Test Cases Correct: 24
-
----------------------------------------------------------------------
   Status   |                    Test Case                           |
----------------------------------------------------------------------
     Ok     | SelfTest_Assert.eq
     Ok     | SelfTest_Assert.notEq
     Ok     | SelfTest_Assert.eqBoolean
     Ok     | SelfTest_Assert.notEqBoolean
     Ok     | SelfTest_utTSQL._clearErrorLog
     Ok     | SelfTest_utTSQL._getCurrentTestPackageAndProcedure
     Ok     | SelfTest_utTSQL._incAssertionCount
     Ok     | SelfTest_utTSQL._logEndTest
     Ok     | SelfTest_utTSQL._logError
     Ok     | SelfTest_utTSQL._logSetupEnd
     Ok     | SelfTest_utTSQL._logSetupStart
     Ok     | SelfTest_utTSQL._logStartTest
     Ok     | SelfTest_utTSQL._logTearDownEnd
     Ok     | SelfTest_utTSQL._logTearDownStart
     Ok     | SelfTest_utTSQL._runTestSetup
     Ok     | SelfTest_utTSQL._runTestTearDown
     Ok     | SelfTest_utTSQL.deleteConfigParameter
     Ok     | SelfTest_utTSQL.getConfigParameter
     Ok     | SelfTest_utTSQL.logAssertion
     Ok     | SelfTest_utTSQL.runTestPackage
     Ok     | SelfTest_utTSQL.runTestCase
     Ok     | SelfTest_utTSQL.runTestSuite
     Ok     | SelfTest_utTSQL.setConfigParameter
     Ok     | SelfTest_utTSQL.getListOfTestCases
----------------------------------------------------------------------
-
- Conclusion:
-
- utTSQL is Ok :-)

If there are any failures it is most likely that the installation failed, double check those install logs.

Otherwise utTSQL is ready to use.

Uninstalling the utTSQL Self Tests

It is possible to remove the self tests, if you feel they are getting in the way of development – as they do take up some room in the Stored Procedures Object window. This is accomplished by opening and running the script file '__UnInstallSelfTestsOnly.Script.sql' located in the utTSQL base directory against the installed database.

This removes the self tests and nothing else.

This will however remove the ability to check utTSQLs integrity easily so its not really recommended, especially if you rebuild databases regularly or switch servers, you never know how an application may behave on different systems/versions/hardware I.e. Regression testing.

Manual Installation

Whats that? Don't like/trust the installer? Yes it is possible to install utTSQL manually, it just takes a bit of time – which is why the installer exists.

You will notice in the base directory of 'utTSQL' there is a file entitled '__InstallOrder.ini' (double underscore) which contains a list of directories (and their order) in which to look for another file entitled '__ScriptOrder.ini' which in turn contain the list of files (and order) in the directory to install.

So it is just a matter of running each sql script file against your chosen database in order.

Uninstalling utTSQL

This will remove utTSQL and the self tests from the database completely, run the script file '__UnInstall.Script.sql' located in the 'utTSQL' base directory from the query window.

Assertions

Assertions are grouped together into test cases which in turn are grouped together in test packages, each test package can have its own setup and tearDown fixtures which are typically used to setup the databases test data.

There are currently four different assertions for use, which are located in the Assert schema:

They are very similar in how they work and the parameters they take, you are expected to pass three parameters:

For example:

BEGIN
	--
	DECLARE @sName NVARCHAR( 30 );
	SET @sName = 'fred';
	--
	EXECUTE Assert.eq @sMessage = 'Check my name is Fred.'
			, @xExpected= 'Fred'
			, @xActual  = @sName;
	--
END;

When this test case runs it will actually fail! (NOTE: you can run the above code snippet directly in the Query window.)

Assert.eq Failed.
	Message : Check my name is Fred.
	EXPECTED: "Fred"
	ACTUAL  : "fred"

Although the name is 'fred' the test is actually looking for 'Fred' with a capital F as the comparisons are case sensitive. Changing the variable value to 'Fred' and NOT the expected result will allow the test to pass correctly.

Boolean data types

Unfortunately MS SQL Server (2008) does not allow BOOLEAN data types (which is clearly stated in the ISO:1999 standard) in table columns or variable and cannot be returned in a result set.

It does provide the BIT data type, but this is not quite a drop in replacement. It can be assigned string values of 'true' and 'false' which in turn are converted to 1 and 0 respectively.

EXECUTE Assert.eq	  @sMessage = 'Check .eq likes boolean values'
			, @xExpected = 1
			, @xActual = 'TRUE';

Will fail with the following result:

Assert.eq Failed.
	Message : Check .eq likes boolean values
	EXPECTED: "1"
	ACTUAL  : "TRUE"

Which is of course wrong, they are equal – so you need to use the Assert.eqBoolean method:

EXECUTE Assert.eqBoolean  @sMessage = "Check .eqBoolean likes boolean values"
			, @bExpected= 1
			, @bActual  = 'TRUE';

This passes as expected.

Both Assert.eqBoolean and Assert.notEqBoolean only take BIT data types as parameters, where as Assert.eq and Assert.notEq take NVARCHAR datatypes as almost all other data types can be converted to NVARCHAR data type including NULLs.

Test Packages

As Transact-SQL does not support packages, schemas are used instead. This allows test cases to be grouped together. For a test package to be found by utTSQL it requires a default 3 character prefix 'ut_', although it is possible to use a prefix of your choice.

CREATE SCHEMA ut_MyTestPackage
AUTHORIZATION dbo;

Will create such a schema with the name 'ut_MyTestPackage'.

To create a test case in this package the procedure name is prefixed with the schema name:

CREATE PROCEDURE ut_MyTestPackage.checkUsersName
	--
AS
BEGIN
	--
	DECLARE @sName NVARCHAR( 30 );
	--
	SELECT @sName = name
	FROM	##ApplicationUsers;
	--
	EXECUTE Assert.eq	  @sMessage = "Check application user name is Fred."
				, @xExpected= 'Fred'
				, @xActual  = @sName;
	--
END;

To run a test package use the procedure 'utTSQL.runTestPackage' and pass in the name of the package to test:

EXECUTE utTSQL.runTestPackage @sPackageName = 'ut_MyTestPackage';

Will result in the Message window displaying:

-   |~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-   |ut_MyTestPackage.checkUsersName
-   |
Msg 208, Level 16, State 0, Procedure checkUsersName, Line 8
Invalid object name '##ApplicationUsers'.
-   |Assertions: 0, Passed: 0, Failed: 0
-   |~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This has happened because we are trying to access a temporary table which does not exist '##ApplicationUsers'.

Setup fixture

Each test package can have its own setup and tearDown test fixtures which are automatically detected and run before and after each test case, this can use to create the required Global Temporary Table.

IF OBJECT_ID( 'ut_MyTestPackage.setup', 'P' ) IS NOT NULL
	DROP PROCEDURE ut_MyTestPackage.setup;
GO

CREATE PROCEDURE ut_MyTestPackage.setup
	--
AS
BEGIN
	--
	CREATE TABLE ##ApplicationUsers (
		  [id]		INTEGER IDENTITY(1,1) NOT NULL
		, [name]	NVARCHAR( 128 )
	);
	--
	INSERT INTO ##ApplicationUsers
	VALUES( 'bert' );
	--
END;

This setup fixture is solely associated with the 'ut_MyTestPackage' package, it will only ever be called for test cases in this package.

Tear Down fixture

The tear down fixture is called after every test case in the test package.

IF OBJECT_ID( 'ut_MyTestPackage.tearDown', 'P' ) IS NOT NULL
	DROP PROCEDURE ut_MyTestPackage.tearDown;
GO

CREATE PROCEDURE ut_MyTestPackage.tearDown
	--
AS
BEGIN
	--
	DROP TABLE ##ApplicationUsers;
	--
END;

Adding the setup and teardown will result in our test failing, as the setup adds 'bert' and not 'Fred' to the '##ApplicationUsers' table.

-   |~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-   |ut_MyTestPackage.checkUsersName
-   |
Assert.eq Failed.
	Message : Check application user name is Fred.
	EXPECTED: "Fred"
	ACTUAL  : "bert"
-   |Assertions: 1, Passed: 0, Failed: 1
-   |~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Changing the value in the setup procedure to 'Fred' allows the test to pass correctly.

-   |~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-   |ut_MyTestPackage.checkUsersName
-   |
-   |Assertions: 1, Passed: 1, Failed: 0
-   |~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Test Cases

A test case is a collection of assertions used to test the behavior of some code, typically the naming convention is one for one in that the test case will have the same name as the code it is testing for easy identification, although describing what the test case actually does also helps! However test cases are often considerably larger than the code they are testing and it may be easier to separate some of the assertions into multiple test cases.

utTSQL allows you to run a test case by itself from the Query window with:

EXECUTE utTSQL.runTestCase @sPackageName   = 'ut_MyTestPackage'
                         , @sProcedureName = 'checkUsersName';

You need to provide the package name and the procedure name of the test case to run. The setup and tearDown fixtures will be automatically called if available.

By default only failed assertions will be displayed in the Message window, there is also a third optional parameter '@bReturnResults' which will return the test results in a table when set to true:

EXECUTE utTSQL.runTestCase	  @sPackageName	  = 'ut_MyTestPackage'
				, @sProcedureName = 'checkUsersName'
				, @bReturnResults = TRUE;

Returns the following results set:

This results set will also lists the failures represented by a '0' in the 'testPassed' column.

Test Suite

In order to run ALL the test in the database the procedure 'runTestSuite' is available, from the query window:

EXECUTE utTSQL.runTestSuite;

This scans the current database for all test packages with the prefix 'ut_' and executes all the test cases within.

The Message window will contain the following when run against the examples provided with utTSQL:

---------------------------------------------------------------------
- Test package name: ut_News
-
-   |~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-   |ut_News.addNews
-   |
-   |Assertions: 24, Passed: 24, Failed: 0
-   |~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-   |ut_News.getLatestNews
-   |
-   |Assertions: 30, Passed: 30, Failed: 0
-   |~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-   |ut_News.getNewsItem
-   |
-   |Assertions: 36, Passed: 36, Failed: 0
-   |~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-
---------------------------------------------------------------------
- Test package name: ut_Users
-
-   |~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-   |ut_Users.addAuthor
-   |
-   |Assertions: 19, Passed: 19, Failed: 0
-   |~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-
---------------------------------------------------------------------
- Total Assertions: 109, Passed: 109, Failed: 0

Using a different prefix

You can also pass in the prefix for the test packages, if you decided not to use the default 'ut_':

EXECUTE utTSQL.runTestSuite @sTestPackagePrefix = 'MyPrefix%';

If you need to use the underscore character as an actual underscore and not a SQL wild character match symbol you need to place a star '*' before it (the * is used as the escape character):

EXECUTE utTSQL.runTestSuite @sTestPackagePrefix = 'MyPrefix*_%';

Installing the example test cases

The provided example is a very simple news content system which allows you to add news items, users and get the latest headlines as well as individual stories.

The example can be located in the 'Example' directory, the installation is identical to that of installing utTSQL:

1. Open a command prompt and navigate to the 'utTransactSQL\Example' directory.
2. Type: __InstallScript.bat[database name]
3. E.g. __InstallScript.bat DEV-05-TransactUnitTester
4. You will then be prompted to continue with the install, press Ctrl + C if you DO NOT!
5. Check that no logs are generated.

This will create four new schemas:

1. News
2. Users
3. ut_News – the tests for the News schema.
4. ut_Users – the tests for the Users schema.

Three tables:

1. News.Article
2. News.Post
3. Users.Author

Four stored procedures:

1. News.addNews
2. News.getLatestNews
3. News.getNewsItem
4. Users.addAuthor

And four test cases:

1. ut_News.addNews
2. ut_News.getLatestNews
3. ut_News.getNewsItem
4. ut_Users.addAuthor

You can also look at the 'SelfTest_' unit tests as a reference point, although it should be noted that these tests can sometimes be a bit odd as they have to check the functionality of utTSQL as it is running!

Running the test suite

To run the test suite in the Query window use:

EXECUTE utTSQL.runTestSuite;

Great, all the tests passed so it must be ready for use...

This is mighty trusting of you, have you looked at the tests to see if they're relevant? Do they even test the functionality?

Remember duff tests are simply that. Duff.

An Exercise!

An interesting exercise would be to go over the provided example and see which tests are relevant and to try and find any holes in the testing. Three immediate ones are:

1. No error handling when using News.addNews() or Users.addAuthor() so you have no idea whether they have added anything to the database, or more precisely WHY they have not (one of the provided parameters is null but which one?).

2. News.addNews() and Users.addAuthor() do not allow NULL values as parameters and wont add the item to the database, but they do allow empty strings '' which for most purposes is the same.

3. The authors email address is not check for email injection when added or retrieved.

Of the three the email injection attack is particularly bad as it is so often overlooked in the database layer, after all its not considered the job of the database to send out emails, just to store them.

It is all to easy to redirect emails or replace the message body, a simple example might be:

Fred@Example.Com%0ABcc:Hacker@Example.com

Whenever Fred is emailed a Blind Carbon Copy of the email is sent to Hacker and because it is a BCC type Fred never knows that Hacker is receiving a copy of his email...

With unit testing you can at least test for these event, but only if you remember to!

There are also a couple of other injection methods to watch out for:

• SQL Injection – which is often used to modify queries to return different results from those intended.
• Javascript Injection – if the database is used to feed a website Javascript can be added to fields (first name for example) which when displayed in a browser are executed.

utTSQL Configuration Parameters

There are several configuration parameters present in utTSQL:

Configuration parameters are set with the procedure utTSQL.setConfigParameter():

EXECUTE utTSQL.setConfigParameter
        @sParameterName  = 'Verbose level'
      , @sParameterValue = 'Failures';

And fetched with the function utTSQL.getConfigParameter():

BEGIN
	--
	DECLARE @sVerboseLevel	NVARCHAR( 128 );
	--
	SET @sVerboseLevel = utTSQL.getConfigParameter( 'Verbose level' );
	-–
END;

Accessing utTSQL from a browser

It is relatively easy to connect a web browser (via a web server) to utTSQL allowing you to run test cases and display the results.

utTSQL.getListOfTestCases

Will return the package name and procedure name of all test cases in the database.

In PHP it is a simple matter to execute and display the results from 'utTSQL.getListOfTestCases', although you will need to replace the name of the database server with that of your own:

<?php
	$sServer	= 'Your Database Server Name';
	$sDatabase	= '[DEV-05-TransactUnitTester]';
	$rsrcHandle = mssql_connect( $sServer ) or die( 'Could NOT connect to SQL Server on: ' . $sServer );
	$bSelected  = mssql_select_db( $sDatabase, $rsrcHandle ) or die( 'Could NOT open database: ' . $sDatabase );

	echo '<hr />You are connected to the <strong>' . $sDatabase . '</strong> database on the <strong>' . $sServer . '</strong>.<hr />' . "\r\n";

	$rsrcStatement	= mssql_init( 'utTSQL.getListOfTestCases', $rsrcHandle );
	$rsrcResult		= mssql_execute( $rsrcStatement );
	
	echo '<br /><strong>List of test cases:</strong><br />' . "\r\n";
	
	while( $aRow = mssql_fetch_assoc( $rsrcResult ) )
	{
		echo '<li>' . $aRow[ 'packageName' ] . '.' . $aRow[ 'procedureName' ] . '</li>' . "\r\n";
	}

	mssql_free_result( $rsrcResult ); 
	mssql_free_statement( $rsrcStatement );	
	mssql_close( $rsrcHandle );
?>

Results in:

utTSQL.runTestCase

Will run a test case and return the a list of all the assertions execute, note the use of variable binding to reduce the chance of a SQL Injection:

<?php
	$sServer	= 'Your Database Server Name';
	$sDatabase	= '[DEV-05-TransactUnitTester]';
	$rsrcHandle = mssql_connect( $sServer ) or die( 'Could NOT connect to SQL Server on: ' . $sServer );
	$bSelected  = mssql_select_db( $sDatabase, $rsrcHandle ) or die( 'Could NOT open database: ' . $sDatabase );

	echo '<hr />You are connected to the <strong>' . $sDatabase . '</strong> database on the <strong>' . $sServer . '</strong>.<hr />' . "\r\n";	

	$sPackageName	= 'ut_Users';
	$sProcedureName	= 'addAuthor ';
	$iReturnResults	= 1;
	$rsrcStatement	= mssql_init( 'utTSQL.runTestCase', $rsrcHandle );
	mssql_bind($rsrcStatement, "@sPackageName", $sPackageName, SQLVARCHAR, false);
	mssql_bind($rsrcStatement, "@sProcedureName", $sProcedureName, SQLVARCHAR, false);
	mssql_bind($rsrcStatement, "@bReturnResults", $iReturnResults, SQLBIT, false);
	
	$rsrcResult = mssql_execute( $rsrcStatement );

	echo '<br /><strong>Results of test case ' . $sPackageName . '.' . $sProcedureName . ' :</strong><br />' . "\r\n";

	while( $aRow = mssql_fetch_assoc( $rsrcResult ) )
	{
		var_dump( $aRow );
	}

	mssql_free_result( $rsrcResult ); 	
	mssql_free_statement( $rsrcStatement );	
	mssql_close($rsrcHandle);
?>

Results in:

Table of Public Procedures and Functions

There are more procedures available but if you notice they all have a underscore '_' character in front, this is used to indicate that the procedure is private and should not be used (it may not be present in the next release).

So if you have to use one be aware that its functionality may change in future, of course there is always the possibility of the public interface changing as well.

Extending the functionality of utTSQL

The most obvious is writing your own Assertions to help deal with the types of objects in your system.

All assertions use to 'utTSQL.logAssertion' log the out come of the assertion.

Bugs

If you think you've found a bug and can replicate it (a solution would be nice too :-), then let us know so we can fix it for everyone.

If you are encountering problems it may be down to quirks in the system, for example NaN (Not a Number) support appears and disappears in different versions of MSSQL Server. So please try to check that this is not the case.

Gotchas!

Savepoint and Rollback

Be careful not to use assertions within a transaction block, as when the rollback occurs your results will be rollback too, doh!

Mock Procedures

Mock procedures are a very good way of isolating functionality for testing, a simple way to achieve this is to move the stored procedure to be mock out of the way with sp_rename:

EXECUTE sp_rename @objname = 'utTSQL.runTestCase' 
                , @newname = 'runTestCase_MovedOutOfWayForMock';

You can then create your own mock procedure in its place.

Obviously care must be taken to remove the mock at the end of the test! A Rollback Transaction will also achieve this for you.

NOTE: Using sp_rename will result in the following message being displayed on the Message window:

Caution: Changing any part of an object name could break scripts and stored procedures.

@@IDENTITY

The @@IDENTITY property will return the value of the INSERTed number in the INDENTITY column of a table (the id of the record just inserted). If the INSERT fails it would be expected to return NULL. However when using conditional INSERTs this is not the case, the value returned for a failed INSERT will be the number of the last inserted record…

BEGIN
	--
	DECLARE @iArticleId	INTEGER;
	DECLARE @iAuthorId	INTEGER;
	DECLARE @sArticle	NVARCHAR( 4000 );
	SET @iArticleId = NULL;
	SET @iAuthorId = NULL;
	SET @sArticle = 'News article to insert';
	--
	INSERT INTO Users.Author	( dateCreated
					, firstName )
	VALUES	( GETDATE()
		, 'Regina' );
	--
	PRINT 'Author ID: ' + CAST( @@IDENTITY AS NVARCHAR );
	--
		INSERT INTO News.Article
			SELECT  @sArticle
			WHERE EXISTS(   SELECT  id
					FROM    Users.Author
					WHERE   id = @iAuthorId );
	--
	SET @iArticleId = @@IDENTITY;
	PRINT 'Article ID: ' + CAST( IsNULL( @iArticleId, -1 ) AS NVARCHAR );
	--
END;

BEGIN
	--
	DECLARE @iArticleId	INTEGER;
	DECLARE @iAuthorId	INTEGER;
	DECLARE @sArticle	NVARCHAR( 4000 );
	SET @iArticleId = NULL;
	SET @iAuthorId = NULL;
	SET @sArticle = 'News article to insert';
	--
	INSERT INTO Users.Author	( dateCreated
					, firstName )
	VALUES( GETDATE()
		, 'Regina'  );
	--
	PRINT 'Author ID: ' + CAST( @@IDENTITY AS NVARCHAR );
	--
		IF EXISTS(	SELECT  id
				FROM    Users.Author
				WHERE   id = @iAuthorId )
		BEGIN
			–
			INSERT INTO News.Article
			VALUES( @sArticle );
			--
			SET @iArticleId = @@IDENTITY;
			–
		END;
	--
	PRINT 'Article ID: ' + CAST( IsNULL( @iArticleId, -1 ) AS NVARCHAR );
	--
END;

Rewritten as above, the code works as expected, although we do have two distinct queries instead of one, but this time @iArticleId is set directly after a unconditional insert statement.

Of course proper use of foreign keys and correct table structure would also eliminate the need for directly test for the author existence, it is just an example but similar code can often be found in live applications.

Grouped Procedures

Grouping Procedures is a very crude form of Method Overloading in Transact-SQL, it allows two or more procedures to have identical names (a semicolon and a number is added to the end of the name). You cannot, however, group functions.

Grouped Procedures are not found by utTSQL, they are not listed in the INFORMATION_SCHEMA.ROUTINES view and according to the CREATE PROCEDURE (Transact-SQL) will be removed from SQL Server in the future (didn't specify a date or version).

This is not to say you can't test a Grouped Procedure just that you cannot have test cases grouped together:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
-- ---------------------------------------------------------------------------
--
IF OBJECT_ID( 'ut_Manager._updateDetails', 'P' ) IS NOT NULL
        DROP PROCEDURE ut_Manager._updateDetails;
GO
--
-- ---------------------------------------------------------------------------
--
CREATE PROCEDURE ut_Manager._updateDetails
        --
AS
BEGIN
        --
        EXECUTE Assert.eq 
                  @sMessage     = '01._updateDetails Check first proc in a Group of Procedures is called.'      
                , @xExpected    = 'pass'
                , @xActual      = 'pass';
        --
END;
GO
--
-- ---------------------------------------------------------------------------
--
CREATE PROCEDURE ut_Manager._updateDetails;2
        --
AS
BEGIN
        --
        EXECUTE Assert.eq 
                  @sMessage     = '01._updateDetails Check second proc in a Group of Procedures is called.'     
                , @xExpected    = 'pass'
                , @xActual      = 'fail';
        --
END;

ut_Manager._updateDetails;2 is never executed (in is not listed in the INFORMATION_SCHEMA.ROUTINES view). You can call it directly with:

EXECUTE ut_Manager._updateDetails;2;

WARNING: The behaviour of SQL and T-SQL commands towards Grouped Procedures is also inconsistent, for example:

DROP PROCEDURE ut_Manager._updateDetails;

Will drop BOTH procedures, it is impossible to drop just the second procedure:

DROP PROCEDURE ut_Manager._updateDetails;2;

Will fail.

It is unfortunate that the disadvantages of Grouped Procedures out weigh the advantages, plus bad syntax (the number 2 looks more like a parameter to be passed in or a typo), it is better to avoid the them entirely.

But testing them is possible after which you can refactor them out :-)

Fin.

Happy unit testing!

Warren Willmey 2008.