DB-level unit tests for SQL Server

I had created some test data on a labmanager VM.  It had taken some time — I had recorded a few calls, an email, and a chat, then tweaked the recording metadata to try out a few important aspects of the stored procedure I was testing.

It’s at these times that I get the nagging feeling — the test was so time-intensive to create and run, why not take the step of automating it so we can run it in the future and catch regressions ‘most instantly?

I wanted an SQL script that I could run that would INSERT the test data as it currently existed on my test machine.  And I didn’t want to create those INSERT statements by hand.  Boy, did I not want to write them all by hand.


Microsoft SQL Server 2005 comes with something called Database Publishing Wizard (look under Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2\ ).  With the following command:

sqlpubwiz script -d MyDatabase -dataonly c:\temp\MyDatabase.sql

…I was able to generate INSERT statements for all the data in my little test database to an .sql script file.  I then edited the file to remove things I didn’t need (and to avoid key collisions by substituting NEWID() calls for hardcoded uniqueidentifiers as necessary).

Finally, with all the data in place, I could add the following tests. (If anything goes wrong, an error message appears in the Messages window.)

DECLARE @tempTable TABLE(BlahId      uniqueidentifier,
                         BlahType        int,
                         BlahDateUTC DateTime,
                         BlahLength  int,
                         PhoneNumber      nvarchar(60),
                         RightsMask       int);

DECLARE	@return_value int;
INSERT INTO @tempTable
EXEC	@return_value = [dbo].[blah_get_blah_chunk]
		@i_sessionId = @sessionId,
		@i_chunkSize = 50;

IF @return_value <> 0 RAISERROR('Return value should be zero but was %d', 15, 1, @return_value);

DECLARE @expectedRecords int;
DECLARE @actualRecords int;
SET @expectedRecords = 5;
SET @actualRecords = (SELECT COUNT(*) FROM @tempTable);
IF @actualRecords <> @expectedRecords RAISERROR('Expected %d records in result set but found %d', 15, 1, @expectedRecords, @actualRecords);

DECLARE @actualDisplayName nvarchar(60);
DECLARE @expectedDisplayName nvarchar(60);
SET @expectedDisplayName = N'blah_user -';
SELECT @actualDisplayName = (SELECT LocalPartyName FROM @tempTable WHERE RecordingId = N'a0ca1a4f-0933-4565-b5da-d8fc3222a42a');
IF @actualDisplayName <> @expectedDisplayName RAISERROR('Expected display name to be "%s" but it was "%s"', 15, 1, @expectedDisplayName, @actualDisplayName);


In the future we could have a script test runner that keeps track of the output and flags errors as part of the build… but one step at a time, right? This already takes test scenarios that took more than a day to set up and makes them runnable in a minute or two.

Database Publishing Wizard can also generate script to create the entire database schema, but it’s the data generation that will support my unit test needs.

Thanks, Microsoft!


Thanks to Pinalkumar Dave for his helpful write-up titled Generate Script with Data from Database – Database Publishing Wizard


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s