Unit testing update stored procedures – part 1

This is the first in a series of blogs that will look at unit testing an ‘update’ sproc using taking a TDD approach.

Testing that the sproc exists and that it has it’s expected properties will be covered – following steps 1 and 2 as per the Database unit testing patterns.

What are the requirements of the sproc
HumanResources.uspUpdateEmployeePersonalInfo from the AdventureWorks2008R2 database will be used as an example.

It updates the HumanResources.Employee table with the values specified in the input parameters for a specified Employee – using the primary key BusinessEntityID.

The SQL script to create it is shown below:

CREATE PROCEDURE [HumanResources].[uspUpdateEmployeePersonalInfo]
@BusinessEntityID [int], 
@NationalIDNumber [nvarchar](15), 
@BirthDate [datetime], 
@MaritalStatus [nchar](1), 
@Gender [nchar](1)
UPDATE [HumanResources].[Employee] 
SET [NationalIDNumber] = @NationalIDNumber 
,[BirthDate] = @BirthDate 
,[MaritalStatus] = @MaritalStatus 
,[Gender] = @Gender 
WHERE [BusinessEntityID] = @BusinessEntityID;
EXECUTE [dbo].[uspLogError];

This sproc will be dropped from the database and recreated from scratch – but taking a TDD type approach.

The ‘end game’ is to recreate the sproc as above (with a couple of minor changes that I will mention along the way).

The first thing is to run the following script to delete it from the test database:

DROP PROCEDURE HumanResources.uspUpdateEmployeePersonalInfo

Step 1 Testing that the sproc exists
A similar approach to that in Unit testing ‘Select’ stored procedures – part 1 is used.

The unit test class – ..\Tests\DatabaseTest.cs – tests for the existence of all expected SQL objects that should be in the database.

As the sproc was previously present its name is already in the list of expected sprocs (‘expectedSprocList’ and the ‘expectedSprocCount’ is already 11).

When this tests are run those related to sproc counts fail as the sproc was deleted so the count is down to 10.

To get the test to pass the following script is run:

CREATE PROCEDURE HumanResources.uspUpdateEmployeePersonalInfo

The tests now pass.

Step 2 Testing it’s properties
A similar approach to that in Unit testing ‘Select’ stored procedures – part 2 is be used.

Take a copy of one of the sample unit test classes included in the DBTestUnit download and rename it to uspUpdateEmployeePersonalInfo_Schema.

Update it appropriately so that the unit test class looks like the following:

using System;
using MbUnit.Framework;
using DBTestUnit.InfoSchema;
using DBTestUnit.UnitTestBaseClass.MSSQL;

namespace AdventureWorks.DatabaseTest.Tests.Sprocs.Schema
    public class uspUpdateEmployeePersonalInfo : SprocTestBase
        public uspUpdateEmployeePersonalInfo()
            dbInstance = "AdventureWorks";
            schema = "HumanResources";
            sprocName = "uspUpdateEmployeePersonalInfo";
            sproc = new Sproc(dbInstance, schema, sprocName);
            expectedInputCount = 5;
            expectedInputList = "@BusinessEntityID,@NationalIDNumber,@BirthDate,@MaritalStatus,@Gender";
            expectedOutputCount = 0;
            expectedOutputList = "";
        [Row("@BusinessEntityID ", "1,IN,int,N/A")]
        [Row("@NationalIDNumber ", "2,IN,nvarchar,15")]
        [Row("@BirthDate", "3,IN,date,N/A")]
        [Row("@MaritalStatus", "4,IN,nchar,1")]
        [Row("@Gender", "5,IN,nchar,1")]
        public void T05_ParameterProperties(string paramName, string expectedParameterProperties)
            string parameterProperties = sproc.ParameterProperties(paramName);
            Assert.AreEqual(expectedParameterProperties, parameterProperties, "Param properties are not as expected");

        [Row("@BusinessEntityID", "HumanResources.Employee", "BusinessEntityID")]
        [Row("@NationalIDNumber", "HumanResources.Employee", "NationalIDNumber")]
        [Row("@BirthDate", "HumanResources.Employee", "BirthDate")]
        [Row("@MaritalStatus", "HumanResources.Employee", "MaritalStatus")]
        [Row("@Gender", "HumanResources.Employee", "Gender")]
        public void T06_ParameterIsTheSameDataTypeAndLength(string paramName, string tableName, string columnName)
            bool areTheSame = sproc.ParameterIsSameAsColumn(paramName, tableName, columnName);
            Assert.IsTrue(areTheSame, "Parameter and column are not the same");

This will test that the sproc’s parameters are as expected – correct number of parameters and properties.

It will also check that the parameters have the same data types as the base table columns that they will update. It is very easy to make a mistake and not have the parameters exactly match the columns (see this blog for more detail).

* The paramater ‘@BirthDate’ in the original sproc – see SQL script above – has a data type of ‘datetime’ whereas the underlying base table column HumanResources.Employee has a type of ‘date’. The sproc created in this series of blogs will have ‘@BirthDate’ as ‘date’.

Compile and run the tests. They will fail as the sproc does not currently have any parameters.

To make it pass the tests the following script is run:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'HumanResources.uspUpdateEmployeePersonalInfo') AND type in (N'P', N'PC'))
DROP PROCEDURE HumanResources.uspUpdateEmployeePersonalInfo

CREATE PROCEDURE HumanResources.uspUpdateEmployeePersonalInfo
@BusinessEntityID [int], 
@NationalIDNumber [nvarchar](15), 
@BirthDate [date], 
@MaritalStatus [nchar](1), 
@Gender [nchar](1)




Run the test again and they now pass as the sproc meets the expectations/requirements as per the tests.

What next
Steps 1 and 2 as per the Database unit testing patterns have been carried out and an initial sproc has been created.

The next blog in this series will look at Step 3 – testing the functionality of the sproc – ensuring that it updates the base table as expected.


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

%d bloggers like this: