Av rating:
Total votes: 49
Total comments: 15

 send to a friend
 printer friendly version

This is the first of three short articles on the subject of the Unit Testing  of TSQL code. The next article will explain in more detail how to use C# and NUnit (the Open-source unit-testing framework for .Net languages)  to unit test result sets returned from stored procedures. The third article will describe how to unit test database modifications, also using C# and NUnit.

Incorrect or incomplete database unit tests may run successfully, but they may not expose all the problems in your application - they can only give you a false sense of security. The following guidelines will help you improve your database unit tests.

Have settings on your Test Server match the Production Server.

Clearly this is an extremely trivial recommendation. However, I cannot emphasize enough how important it is. Discrepancies in database settings may cause your application to silently behave differently, without raising any errors - such problems may be very difficult to detect. For instance, the following code snippet will run differently against databases with different default collations:

CREATE TABLE t(a CHAR(1), b CHAR(1))
INSERT t(a, b) VALUES('a', 'A')
SELECT COUNT(*) FROM t WHERE a=b

You can create the following two databases, run the code snippet against both, and see for yourself that it returns 1 on your case insensitive database, and 0 on your case sensitive one:

CREATE DATABASE [TestCI] COLLATE SQL_Latin1_General_CP1_CI_AS
GO
CREATE DATABASE [TestCS] COLLATE SQL_Latin1_General_CP1_CS_AS
GO

There are many other settings that may affect your application, such as default isolation level (READ COMMITTED vs. READ COMMITTED SNAPSHOT), and settings on nested and/or recursive triggers. Providing a complete list of settings and explaining how they may affect your application is beyond the scope of this article. For an example on how snapshot isolation may affect your modifications, refer to a series of Hugo Kornelis's posts named " Snapshot isolation: A threat for integrity?" and published on sqlblog.com. I describe how settings on nested and/or recursive triggers have broken an application in my article " Avoid These Common Business Rule Implementation Mistakes"

(http://www.devx.com/dbzone/Article/31985/)

Impersonate your user when you test.

Let's suppose that you created a stored procedure but forgot to grant any privileges to execute it. When you, the developer, then unit test your code against your development server, you may connect as a dbo - this is a common practice. Unfortunately, so long as you connect as dbo, you will not detect your missing permissions. This problem is very easy to fix. For example, you can just create a mock user and add it to your database role.

CREATE USER mockuser WITHOUT LOGIN 
GO 
sp_addrolemember @rolename = 'YourRoleName', @membername = 'mockuser' 
GO

In your unit test, impersonate the mock user before executing the stored procedure, as follows:

SETUSER 'mockuser'

If the permission to execute your stored procedure is not granted to either the mock user or to the role, you will get an exception. Do not forget to revert to dbo after running the test:

SETUSER

Note: This is a just very simple example of impersonation. It is good to use when you just run your unit tests on your local machine. It is not a substitute for a more realistic test environment when a real network account is used. A more detailed discussion of database security and impersonation is beyond the scope of this article.

Populate enough test data before your test.

We'll suppose that you have failed to provide a WHERE clause in your stored procedure:

/*
I deliberately stripped this stored procedure from all the bells and
             whistles that are not relevant to the point I am making.
*/
CREATE PROCEDURE Writers.DeleteCustomer @CustomerID INT
AS
DELETE FROM Data.Customers --WHERE clause is missing
RETURN @@ERROR
GO

If you only had one row in your table before running the stored procedure, your error would not be detected by your unit test. Make sure you have enough test data before running the test. After running the test, make sure that all the data except for the one deleted row is still in the table.

Test how your code handles errors.

Now suppose that you have changed your DeleteCustomer stored procedure, as follows:

CREATE PROCEDURE Writers.DeleteCustomer @CustomerID INT
AS
DECLARE @error INT, @rowcount INT
DELETE FROM Data.Customers WHERE CustomerID = @CustomerID
SELECT @error = @@ERROR  -- forgot to populate @rowcount = @@ROWCOUNT
IF @rowcount = 0 BEGIN
  RAISERROR('Could Not Find Customer', 16, 1)
  SET @error = -1
END
RETURN @error
GO

Suppose you want to verify that you get an exception if you provide invalid customer ID, and you have written a unit test in C# using NUnit, as follows:

[Test]
[ExpectedException(typeof(SqlException))]
public void TestDeleteCustomerInvalidID()
{
            SqlCommand cmd = this.connection.CreateCommand();
            cmd.CommandText = "EXEC Writers.Delete_Customer -1";
            cmd.ExecuteNonQuery();
}

Apparently the test succeeds, but wait a moment: because the name of the stored procedure is misspelled, you are getting the wrong kind of SQL exception! Your procedure did not even execute, and your unit test should not succeed. The following unit test verifies that the right exception is raised:

[Test]
public void TestDeleteCustomerInvalidID()
{
            bool gotTheRightException = false;
            cmd.CommandText = "EXEC Writers.Delete_Customer -1";
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                string s = e.Message;
                gotTheRightException = 
                  s.Contains("Could Not Find Customer");
            }
            Assert.IsTrue(gotTheRightException);   
            //You still need to verify that no one row is deleted
}

Note that even though you verified that you received the right exception, you still need to verify that no rows are deleted. The reason is simple:

ALTER PROCEDURE Writers.DeleteCustomer @CustomerID INT
AS
DECLARE @error INT, @rowcount INT
DELETE FROM Data.Customers 
-- deliberately removed WHERE CustomerID = @CustomerID
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
IF @rowcount <> 1 BEGIN
  RAISERROR('Could Not Find Customer', 16, 1)
  SET @error = -1
END
RETURN @error
GO

This incorrect procedure first deletes all the rows and then raises the right exception if there were more than one row in the table. For more details on error handling in Transact-SQL refer to articles on Erland Sommarskog's Web site.

Testing stored procedures which modify data will be described in more detail in a later article.

Do Not Forget To Consider Concurrency.

In some cases, stored procedures, will work as expected as long as you unit test them from a single connection, but fail intermittently in a more realistic multi-connection environment. For example, consider a requirement to implement a stored procedure that should return a unique integer as an output parameter every time it is called.

The following three simple stored procedures all attempt to implement the requirement:

CREATE TABLE Data.Counter(Counter INT NOT NULL)
INSERT Data.Counter(Counter) VALUES(1)
GO
CREATE PROCEDURE Writers.GetNextCounter1 @NextCounter INT OUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
UPDATE Data.Counter SET Counter = Counter + 1
SELECT @NextCounter = Counter FROM Data.Counter
COMMIT
GO
CREATE PROCEDURE Writers.GetNextCounter2 @NextCounter INT OUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT @NextCounter = Counter + 1 FROM Data.Counter
UPDATE Data.Counter SET Counter = Counter + 1
GO
CREATE PROCEDURE Writers.GetNextCounter3 @NextCounter INT OUT
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
UPDATE Data.Counter SET Counter = Counter + 1
SELECT @NextCounter = Counter FROM Data.Counter
COMMIT
GO

If you run any one of these three stored procedures from a single connection only, all three would appear to work correctly. However, if you run them thousands of times simultaneously from several connections, the first one will still reliably provide unique numbers, while the second one may provide some duplicates, and the third one is likely to end up in a deadlock.

As you have seen, simple unit testing from one connection is not sufficient in this case.

The next article will explain in more detail how to utilize C# and NUnit to unit test result sets returned from stored procedures.