Unit testing the data outputted by a stored procedure

Introduction
The previous blog in this series gave a general introduction into taking a TDD approach when testing the expected data outputs for a new sproc.

This blog will look into this in more area detail – in particular looking at:

• Testing each query filter in a ‘SELECT’ statement.
• Setting up appropriate test data to do this.

It is assumed that you have read the previous blogs in this series.

Note
As mentioned previously there are two areas to think about when testing the data outputted by a SQL object:

A) The data elements/columns returned.

B) The query filters required to change the data set returned.

The previous blog whilst being good for A) there are outstanding issues with part B).

Only one test was developed and for the chosen department the sproc returns the expected data set ie it returns ‘current employees for a given department’.

If another department is chosen, eg DepartmentId=10, and the sproc executed as shown:

EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=10

As can be seen in the image below, the sproc incorrectly returns people that have left the department as seen in the first row (EndDate’ is in the past).

The department chosen for the initial test in the previous blog just happened to have a ‘nice set’ of test data ie there were no employees that had left that department.

This shows the importance of having appropriate test coverage.

Background scenario
At this point there is a need to think about what is meant by ‘current employees for a given department’ and to carry out some further analysis of the current AdventureWorks database design.

The system* is required to record when a person has:

• Left the firm.
• Left an individual department to join another one eg an internal department transfer.
• Indicated that they will carry out an internal department transfer in the future.

In general terms the sproc should only return those people:

A) Who have not left the firm.
B) Who are currently working for a given department.

The data set returned by the sproc is created by joining the HumanResources.Employees and HumanResources.EmployeeDepartmentHistory tables and filtering by DepartmentId. Effectively this returns ALL people that have ever worked for a given department.

Looking at these two tables in a little more detail.

• HumanResources.Employees. Contains a column ‘CurrentFlag’. A value of ‘1’ indicates that a person is still with the company – ‘0’ they have left.
• HumanResources.EmployeeDepartmentHistory. This contains two columns of interest – ‘StartDate’ and ‘EndDate’. If ‘EndDate’ is ‘Null’ or the value is in the future it indicates that the person is still with the department.

For a given department what data should be included and conversely what should be excluded in the dataset returned?

This can be broken down into 4 areas (a short-hand description is given first and will be used throughout the rest of this blog):

• ‘Exclude company leavers’.Only include people currently with the company – exclude anyone who has left (indicated by the ‘CurrentFlag’).
• ‘Exclude department leavers’**Only include people currently working for the department – exclude those that have left started the department (indicated by the ‘EndDate’ being in the past).
• ‘Exclude future department joiners’Only include people currently working for the department – exclude those that will join in the future (indicated by the ‘StartDate’ being in the future).
• ‘Include future department leavers’**Include people currently working for the department – even if they will leave in department in the future (indicated by the ‘EndDate’ being in the future).

Each one the above will be tested.

* The purpose of this blog is to show how detailed data output testing can be carried out using DBTestUnit. Therefore I am creating a scenario where multiple ‘query filters’ are required.

** Due to ‘EndDate’ allowing nulls – both these scenarios need to be tested.

Setting up test data
In the previous blog the existing AdventureWorks data was used. To explicitly test each of the scenarios outlined above further test data needs to be created.

4 new departments are created – 1 new department for each ‘query filter’ to test. Each new department will also have 2 new people created. One row should be returned and one shouldn’t. This setup ensures that the data sets returned by tests are exclusive with no risk of overlap.

The four new departments are shown below:

The people created for each new department and the requirements that they will test are shown below.

‘Exclude company leavers’ – DepartmentID 17

The first person – with ID=20778 – CurrentFlag=0 indicates that they have left the firm. This row should not be returned.

Note that their ‘EndDate’ has deliberately been left as ‘Null’. This ensures only testing of the fact that they have left the firm and not that they have left the department.

‘Exclude department leavers’ – DepartmentID=18

The first person – ID=20780 – ‘EndDate’ in the past indicates that they have left the department. This row should not be returned.

‘Exclude future department joiners’ – DepartmentID=19

The first person – ID=20782 – ‘StartDate’ in the future – indicates that they have not joined the department yet.This row should not be returned.

‘Include future department leavers’ – DepartmentID=20

The first person – ID=20784 – ‘EndDate’ in the future. Both rows should be in returned.

* If you would like to repeat this testing contact me and I will provide you with the SQL scripts used to create the test data above.

Creating the expected test datasets using the ExportDBDataAsXML tool
Next the expected results test data XML files are created using ExportDBDataAsXML provided with DBTestUnit. Four tests will be written – therefore 4 expected test result XML files are created.

The four XML files created are called the following:

TestData_uspGetCurrentEmployeesForDepartment_ExcludeCompanyLeavers
TestData_uspGetCurrentEmployeesForDepartment_ExcludeDeptLeavers
TestData_uspGetCurrentEmployeesForDepartment_ExcludeFutureJoiners
TestData_uspGetCurrentEmployeesForDepartment_IncludeFutureLeavers

The following is added to the XML export tool config. The SQL query used is similar to that used in the previous blog but appropriate filters. To save space the full query is included with the first one – the others have a place holder of ‘[SAME SQL AS ABOVE]’.

 <SQLObject>
    <FileName>TestData_uspGetCurrentEmployeesForDepartment_ExcludeCompanyLeavers</FileName>
    <Execute>Yes</Execute>
    <SQLStatement>
      SELECT e.BusinessEntityID
      , p.FirstName
      , p.LastName
      , e.JobTitle
      , ed.StartDate
      , ed.EndDate
      FROM HumanResources.EmployeeDepartmentHistory as ed
      INNER JOIN HumanResources.Employee as e on ed.BusinessEntityID=e.BusinessEntityID
      INNER JOIN Person.Person as p on e.BusinessEntityID = p.BusinessEntityID
      WHERE ed.DepartmentID = 17
      AND e.CurrentFlag=1
    </SQLStatement>
  </SQLObject>

  <SQLObject>
    <FileName>TestData_uspGetCurrentEmployeesForDepartment_ExcludeDeptLeavers</FileName>
    <Execute>Yes</Execute>
    <SQLStatement>
	[SAME SQL AS ABOVE]
      WHERE d.DepartmentID = 18
      AND (ed.EndDate IS NULL OR ed.EndDate = GETDATE())
    </SQLStatement>
  </SQLObject>

  <SQLObject>
    <FileName>TestData_uspGetCurrentEmployeesForDepartment_ExcludeFutureJoiners</FileName>
    <Execute>Yes</Execute>
    <SQLStatement>
	[SAME SQL AS ABOVE]
      WHERE d.DepartmentID = 19
      AND ed.StartDate &lt; GETDATE()
    </SQLStatement>
  </SQLObject>

  <SQLObject>
    <FileName>TestData_uspGetCurrentEmployeesForDepartment_IncludeFutureLeavers</FileName>
    <Execute>Yes</Execute>
    <SQLStatement>
	[SAME SQL AS ABOVE]
      WHERE d.DepartmentID = 20
    </SQLStatement>
  </SQLObject>

When run, ExportDBDataAsXML will create the 4 XML files, containing the expected test results, and output them to a specified directory.

Once created they should be visibly inspected to ensure that they contain the expected data.

The following show the contents of the XML file containing the expected data set to be returned by the sproc for the test scenario: ‘Exclude company leavers’ – DepartmentID=17

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table>
    <BusinessEntityID>20779</BusinessEntityID>
    <FirstName>Joe</FirstName>
    <LastName>Doe</LastName>
    <JobTitle>TestJobTitle</JobTitle>
    <StartDate>2001-12-12T00:00:00+00:00</StartDate>
  </Table>
</NewDataSet>

Writing the DBTestUnit tests
4 new ‘RowTests’ are added to the existing test class – ‘uspGetCurrentEmployeesForDepartment’ – created in the previous blog.

As seen in the sample code below each has the name of the XML file with the expected/required results and the DepartmentId that will be used in the sproc parameter.

using System;
using MbUnit.Framework;
using DBTestUnit.Util;

namespace AdventureWorks.DatabaseTest.Tests.Sprocs.Functional
{
    [TestFixture]
    public class uspGetCurrentEmployeesForDepartment
    {
        string dbInstance = "AdventureWorks";
        string sprocTestFileDir = AppSettings.DirSprocExpectedResults();

        public uspGetCurrentEmployeesForDepartment() { }

        [RowTest]
        [Row("Dept2", "2")]
        [Row("ExcludeCompanyLeavers", "17")]
        [Row("ExcludeDeptLeavers", "18")]
        [Row("ExcludeFutureJoiners", "19")]
        [Row("IncludeFutureLeavers", "20")]
        public void T01_CheckRowsFromExec(string fileNameSuffix, string sqlParam)
        {
            string fileName = "TestData_uspGetCurrentEmployeesForDepartment_" + fileNameSuffix;
            string sqlText = "EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=" + sqlParam;
            bool areTheSame = DataSetComparer.Compare(sprocTestFileDir, fileName, dbInstance, sqlText);
            Assert.IsTrue(areTheSame, "The dataset created by the SQL run in this test is not the same as that from the file created previously.");
        }
    }
}

Initially running the tests
The image below shows the MBUnit GUI when these tests are run.

1 test passes*. The current implementation meets the requirement to ‘Include future department leavers’.

3 of the tests fail. For example, it fails for ‘Exclude company leavers’.

Running the following SQL statement shows why:

EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=17

As can be seen in the image below – the data set returned still includes the test row added for the person that has left the company.

* Ideally a ‘failing test’ should be written first. To make it easier to write this blog I have written and implemented all 4 tests in one go. In reality they would often be carried out one by one. This test is included to get around the fact of ‘EndDate’ being nullable. As will be shown below, to implement ‘Exclude future department joiners’ the following query filter is used:

ed.EndDate IS NULL

When this is added to the sproc, without including:

OR ed.EndDate >= GETDATE())

this test would then fail.

Making all the tests pass
The existing sproc can be amended using a script similar to that shown below:

IF EXISTS(SELECT ROUTINE_NAME 
		FROM INFORMATION_SCHEMA.ROUTINES
		WHERE ROUTINE_TYPE = 'PROCEDURE'
		AND ROUTINE_SCHEMA + '.' + ROUTINE_NAME = 'HumanResources.uspGetCurrentEmployeesForDepartment')
	DROP PROC HumanResources.uspGetCurrentEmployeesForDepartment
GO

CREATE PROC HumanResources.uspGetCurrentEmployeesForDepartment
	@departmentID smallint
AS

SET NOCOUNT ON

SELECT e.BusinessEntityID
, p.FirstName
, p.LastName
, e.JobTitle
, ed.StartDate
, ed.EndDate
FROM HumanResources.EmployeeDepartmentHistory as ed
INNER JOIN HumanResources.Employee as e on ed.BusinessEntityID=e.BusinessEntityID
INNER JOIN Person.Person as p on e.BusinessEntityID = p.BusinessEntityID
WHERE ed.DepartmentID = @departmentID
AND e.CurrentFlag=1
AND (ed.EndDate IS NULL OR ed.EndDate >= GETDATE())
AND ed.StartDate <= GETDATE()

GO

The following filters have been added:

• ‘Exclude company leavers’

  e.CurrentFlag=1 

• ‘Exclude future department joiners’ and ‘Include future department leavers’

  (ed.EndDate IS NULL OR ed.EndDate >= GETDATE())

• ‘Exclude future department joiners’

  ed.StartDate <= GETDATE()

When the tests are run again they now all pass as shown in the image below:

Now if the following SQL statement is run again:

EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=17

As can be seen in the image below, the sproc now only returns one row – no longer returning a row for person who has left the company.

Summary
This blog has looked at how DBTestUnit can be used to explicitly test query filters when testing the data outputted by a sproc.

At first this type of testing might seem cumbersome and perhaps, to some, even an overkill:

But I find this approach offers a number of advantages including:

• As tests have to be written first it tends to ensure that there is a focus on the exact requirements of the sproc.
• The tests act as documentation. This is really useful if future changes are required.

What next
The next blog in the series will take a look at the final part from Database unit testing patterns – how to take a TDD approach for testing the permissions/security of a sproc.

Unit testing ‘Select’ stored procedures – part 3

Step 3 – unit testing the functionality of the a new sproc
This is the third blog in a series looking at how to take a TDD approach when creating new sprocs.

It will look at the third step outlined in – Database unit testing patterns – how to test the functionality of a sproc. In this case, how to ensure that the data outputted meets the requirements.

* It is assumed that you have read parts 1 and 2 in this series.

Overview of the overall process

• Identify the requirements – the data that should be returned by the sproc.
• Ensure that appropriate test data has been set up.
• Use the ExportDBDataAsXML tool to create XML files that contain the datasets that should be returned by the sproc.
• Write the data comparison unit tests using DBTestUnit. When first run these will fail.
• Write the implementation SQL script for the sproc.
• Run the tests again. They will pass if the data returned by the sproc matches the ‘expected test data’ in the XML files.

Why test the data outputted by the sproc
The data outputted by a sproc is part of the data contract – the ‘DB API’ – offered by the database to its client. When changes/refactorings are made to the database, having a set of automated unit tests can make it easier to ensure that this DB API is maintained.

Also, from a personal viewpoint, I find that writing tests first – ie defining the requirements – means the implementation is more likely to meet the requirements.

Note
The purpose of this blog is to give an initial introduction into taking a TDD approach when carrying out data comparison type unit testing.

The implementation produced in this blog will not have all of the required query filters to ensure that only ‘current employees are returned’. The next blog in this series will show how to create more ‘granular’ tests – with each query filter explicitly unit tested.

Background scenario
The high level requirement can be outlined as:

“For a given department the sproc should return the details of current employees in that department.”

To test the data that will be outputted there are two areas that need defining:

a) The data elements that the sproc should return. This along with the properties tested in the previous blog effectively make up the ‘DB API’ – the contract offered by the sproc to clients of it.

b) The logic within the sproc that ensures only ‘current employees’ of a department are returned ie the query filters.

The following data elements are required to be returned by the sproc*:

BusinessEntityID (unique ID of a person) FirstName, LastName, JobTitle, StartDate and EndDate (of working for a particular department).

* In reality to get to this point can involve a large degree of analysis/collabaration between different people.

Ensuring that test data exists
The next step is to ensure that appropriate test data exists.

The list of data elements above can be sourced from the existing AdventureWorks database using the following tables:

Person.Person, HumanResources.Employee and HumanResources.EmployeeDepartmentHistory

The following SQL statement can be used to return the data that is expected for the department with a DepartmentID = 2

SELECT e.BusinessEntityID
, p.FirstName
, p.LastName
, e.JobTitle
, ed.StartDate
, ed.EndDate
FROM HumanResources.EmployeeDepartmentHistory as ed
	INNER JOIN HumanResources.Employee as e on ed.BusinessEntityID=e.BusinessEntityID
	INNER JOIN Person.Person as p on e.BusinessEntityID = p.BusinessEntityID
WHERE ed.DepartmentID = 2

The image below shows the data that is returned when this query is run against the database:

At this point, the data currently in the sample database is appropriate for carrying out initial testing

The next blog will look at actually creating data to test specific query filters.

Creating the expected test datasets
The ‘expected test data’, that the sproc should return, will be stored in XML/XSD files.

The ExportDBDataAsXML tool can be used to create these. More detail on how to set up/configure this tool can be found at – Using DBTestUnit to test data outputs from SQL objects.

For testing the new sproc the following is added to the ExportDBDataAsXML config file.

<!--********************-->
<!--SECTION FOR SPROCS-->
<!--********************-->
<Sprocs>

  <SQLObject>
    <FileName>TestData_uspGetCurrentEmployeesForDepartment_Dept2</FileName>
    <Execute>Yes</Execute>
    <SQLStatement>
      SELECT e.BusinessEntityID, p.FirstName, p.LastName, e.JobTitle, ed.StartDate, ed.EndDate
      FROM HumanResources.EmployeeDepartmentHistory as ed
      INNER JOIN HumanResources.Employee as e on ed.BusinessEntityID=e.BusinessEntityID
      INNER JOIN Person.Person as p on e.BusinessEntityID = p.BusinessEntityID
      WHERE ed.DepartmentID = 2
    </SQLStatement>
  </SQLObject>

When the tool is run it queries the database using the SQL set in the ‘SQLStatement’ element. The output is placed in XML/XSD files – with the names set by the ‘FileName’ element (the directory it is created in is configured in another section.)

The data outputted is shown below for the XML and XSD files respectively:

TestData_uspGetCurrentEmployeesForDepartment_Dept2.xml

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table>
    <BusinessEntityID>4</BusinessEntityID>
    <FirstName>Rob</FirstName>
    <LastName>Walters</LastName>
    <JobTitle>Senior Tool Designer</JobTitle>
    <StartDate>2004-07-01T00:00:00+01:00</StartDate>
  </Table>
  <Table>
    <BusinessEntityID>11</BusinessEntityID>
    <FirstName>Ovidiu</FirstName>
    <LastName>Cracium</LastName>
    <JobTitle>Senior Tool Designer</JobTitle>
    <StartDate>2005-01-05T00:00:00+00:00</StartDate>
  </Table>
  <Table>
    <BusinessEntityID>12</BusinessEntityID>
    <FirstName>Thierry</FirstName>
    <LastName>D'Hers</LastName>
    <JobTitle>Tool Designer</JobTitle>
    <StartDate>2002-01-11T00:00:00+00:00</StartDate>
  </Table>
  <Table>
    <BusinessEntityID>13</BusinessEntityID>
    <FirstName>Janice</FirstName>
    <LastName>Galvin</LastName>
    <JobTitle>Tool Designer</JobTitle>
    <StartDate>2005-01-23T00:00:00+00:00</StartDate>
  </Table>
</NewDataSet>

TestData_uspGetCurrentEmployeesForDepartment_Dept2.xsd

<?xml version="1.0" standalone="yes"?>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:Locale="">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="Table">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="BusinessEntityID" type="xs:int" minOccurs="0" />
              <xs:element name="FirstName" type="xs:string" minOccurs="0" />
              <xs:element name="LastName" type="xs:string" minOccurs="0" />
              <xs:element name="JobTitle" type="xs:string" minOccurs="0" />
              <xs:element name="StartDate" type="xs:dateTime" minOccurs="0" />
              <xs:element name="EndDate" type="xs:dateTime" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>

Once these files have been created they should be checked to ensure they are returning the expected data.

Writing a test that defines the requirement/expectations
The next step is to write the data comparison unit tests.

As mentioned in previous blogs DBTestUnit provides a number of sample C# test templates. Therefore, the easiest way to get started is to copy one and change appropriately.

The sample code below shows a data comparison test for the new sproc:

using System;
using MbUnit.Framework;
using DBTestUnit.Util;

namespace AdventureWorks.DatabaseTest.Tests.Sprocs.Functional
{
    [TestFixture]
    public class uspGetCurrentEmployeesForDepartment
    {
        string dbInstance = "AdventureWorks";
        string sprocTestFileDir = AppSettings.DirSprocExpectedResults();

        public uspGetCurrentEmployeesForDepartment() { }

        [RowTest]
        [Row("TestData_uspGetCurrentEmployeesForDepartment_Dept2", "EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=2")]
        public void T01_CheckRowsFromExec(string fileName, string sqlText)
        {
            bool areTheSame = DataSetComparer.Compare(sprocTestFileDir, fileName, dbInstance, sqlText);
            Assert.IsTrue(areTheSame, "The data returned by the SQL text run in this test is not the same as that in the XML file.");
        }
    }
}

What happens when the tests are run?
When the test is first run it will fail.

As executing the following SQL statement:

‘EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=2’

will not return any data. Obviously this will not be the same when compared to the data contained in the expected test data XML/XSD files created above.

The image below shows the output from the MBUnit console when this test is run

Making the test pass
Next just enough SQL is written to ensure that the test passes.

A script – as shown below – is created and run against the database.

IF EXISTS(SELECT ROUTINE_NAME 
			FROM INFORMATION_SCHEMA.ROUTINES
			WHERE ROUTINE_TYPE = 'PROCEDURE'
			AND ROUTINE_SCHEMA + '.' + ROUTINE_NAME = 'HumanResources.uspGetCurrentEmployeesForDepartment')
	DROP PROC HumanResources.uspGetCurrentEmployeesForDepartment
GO

CREATE PROC HumanResources.uspGetCurrentEmployeesForDepartment
	@departmentID smallint
AS

SET NOCOUNT ON

SELECT e.BusinessEntityID
, p.FirstName
, p.LastName
, e.JobTitle
, ed.StartDate
, ed.EndDate
FROM HumanResources.EmployeeDepartmentHistory as ed
	INNER JOIN HumanResources.Employee as e on ed.BusinessEntityID=e.BusinessEntityID
	INNER JOIN Person.Person as p on e.BusinessEntityID = p.BusinessEntityID
WHERE ed.DepartmentID = @departmentID

GO

When the test is run again – it will now pass.

Now, the data returned by the sproc is the same as that contained in the expected test data files.

This is shown in image below:

How does this work?
There are two parts to a data comparison unit test as shown in the sample code below:

        [RowTest]
        [Row("TestData_uspGetCurrentEmployeesForDepartment_Dept2", "EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=2")]
        public void T01_CheckRowsFromExec(string fileName, string sqlText)
        {
            bool areTheSame = DataSetComparer.Compare(sprocTestFileDir, fileName, dbInstance, sqlText);
            Assert.IsTrue(areTheSame, "The data returned by the SQL text run in this test is not the same as that in the XML file.");
        }

1. At the top – the ‘Row’ part.
The names of the expected data test file and the actual SQL statement that will be executed are set.

The XML/XSD files named:

‘TestData_uspGetCurrentEmployeesForDepartment_Dept2’

will be compared to the data returned by executing:

‘EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=2’

2. The DBTestUnit test method that will carry out the data comparison.
The values set above are passed to the test method – ‘DataSetComparer.Compare’ (part of the DBTestUnit.Util namespace).

This method ‘grabs’ the expected data from the XML/XSD files and transforms it into datasets and then compares this to the dataset returned by running the SQL statement against the database.

If the datasets are the same the test method will return ‘true’ and the test will pass.

If there are any differences it will return ‘false’ and the test will fail.

What happens if ‘unexpected’ changes are made?
There are two types of changes which these types of tests will identify.

1. Schema changes of the dataset returned. If this is changed in anyway eg column names, ordinal position, adding/removing a column and data types*.

2. Logic changes that cause the number of rows returned to change. For a given input there is an expected set of rows to be returned. If, for example, a change is made to a query filter that effects the rows returned – then this type of test will quickly identify this.

* Due to the method currently used by DBTestUnit to compare datasets actually returned by the sproc to those which are expected (XML/XSD comparison) – some types of schema changes on the dataset returned by sproc will not be detected. Looking at the XSD – TestData_uspGetCurrentEmployeesForDepartment_Dept2.xsd shown above – if the underlying SQL column for ‘LastName’ is changed, for example, from nvarchar(50) to nvarchar(40) this type of test will not necessarily identify this. I hope to improve on this in a future version.

Acknowledgements
The idea behind how to carry out this data comparison and the implementation used within the DBTestUnit method – DataSetComparer.Compare – was based on an article written by Alex Kuznetsov and Alex Styler Close those Loopholes – Testing Stored Procedures.

Future release
The next release of DBTestUnit – post version – 0.4.0.428 – will use an updated version of MBUnit (from v2.4 to v3.2).

The latest version of MBUnit makes it easier to work with XML – see MbUnit 3.2 makes it easier to work with Xml

For example it has an inbuilt test method – Assert.Xml.AreEqual. The advantage of using this is the fact that if the assert fails it will display any differences between two XML files. This can save a lot of time troubleshooting failing tests. Writing a test will be very similar to the sample code shown above.

Note the existing method DataSetComparer.Compare will continue to be supported.

What next
This blog has given a quick overview on how to take a TDD approach when testing the expected data that should be outputted by a new sproc.

The next blog in this series will extend this and take a more detailed look at how to explicitly test each ‘query filter’.

Unit testing ‘Select’ stored procedures – part 2

Step 2 – testing the properties of a new sproc

Intro
This blog is the second in this series looking at how to take a TDD approach when creating a new sproc.

The first blog showed how DBTestUnit could be used to test for the existence of a SQL object. This blog will look at the second step – outlined in Database unit testing patterns – how to test the properties of a new sproc.

Why test the properties/schema of a sproc
Sprocs can be used as a way of decoupling external clients from the internal schema of the database. They effectively offer a ‘DB API’ to clients – with the ‘allowable’ inputs and outputs being part of the ‘data contract’ between the database and the external clients.

The benefits of taking a TDD approach include:
1. The sproc developer has to think explicitly about the contract offered to clients.
2. When changing/refactoring the internal schema of a database, having a set of automated tests can help ensure that the ‘DB API’ is maintained .

Background scenario
The overall high level requirement was outlined in the first blog.

“For a given department the sproc should return the details of current employees in that department.”

The new sproc created at the end of the first blog first blog met the basic requirement that it existed. The next step is to define the inputs/outputs required.

From the existing database schema it can be seen that each department can be uniquely identified by the column DepartmentID. From this the following requirements can be stated:

“The sproc should only have 1 input parameter – named @departmentID.”

“It should have 0 output parameters”.

“The input parameter @departmentID should have the same properties as that of the column – HumanResources.Department.DepartmentID”

“The input parameter @departmentID should have the data type – smallint”

Note
The focus of this blogs is on the TDD process – not on the actual sproc being created. Therefore, a simple sproc is being used as an example and it will be implemented using the existing database schema.

The blog series is being written in a serial fashion – i.e. in step 2 (this blog) the inputs/outputs are defined, in step 3 (the next blog) the sprocs functionality is defined. As mentioned previously – Database unit testing patterns – in reality there is often a number of iterations between steps 2 and 3.

Writing a test that defines the requirement/expectations
DBTestUnit provides a number of sample C# sproc test templates. Therefore, the easiest way to get started is to copy one and change the appropriate expected properties for the new sproc.

The sample code below shows the tests for the requirements outlined above:

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

namespace AdventureWorks.DatabaseTest.Tests.Sprocs.Schema
{
    [TestFixture]
    public class uspGetCurrentEmployeesForDepartment : SprocTestBase
    {
        public uspGetCurrentEmployeesForDepartment()
        {
            dbInstance = "AdventureWorks";
            schema = "HumanResources";
            sprocName = "uspGetCurrentEmployeesForDepartment";
            sproc = new Sproc(dbInstance, schema, sprocName);
            expectedInputCount = 1;
            expectedInputList = "@departmentID";
            expectedOutputCount = 0;
            expectedOutputList = "";
        }

        [RowTest]
        [Row("@departmentID", "1,IN,smallint,N/A")]
        public void T05_ParameterProperties(string paramName, string expectedParameterProperties)
        {
            string parameterProperties = sproc.ParameterProperties(paramName);
            Assert.AreEqual(expectedParameterProperties, parameterProperties, "Param properties are not as expected");
        }

        [RowTest]
        [Row("@departmentID", "HumanResources.Department", "DepartmentID")]
        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");
        }
    }
}

To make it easier to explain the code above, the explanation notes have been split into three sections.

Section A

            expectedInputCount = 1;
            expectedInputList = "@departmentID";
            expectedOutputCount = 0;
            expectedOutputList = "";

This is declarative and similar in nature to the tests outlined in the first blog in this series.

It tests the requirements:

“The sproc should only have 1 input parameter – named @departmentID.”

expectedInputCount = 1 and expectedInputList = “@departmentID”;

“It should have 0 output parameters”

expectedOutputCount = 0 and expectedOutputList = “”

Section B

        [RowTest]
        [Row("@departmentID", "1,IN,smallint,N/A")]
        public void T05_ParameterProperties(string paramName, string expectedParameterProperties)
        {
            string parameterProperties = sproc.ParameterProperties(paramName);
            Assert.AreEqual(expectedParameterProperties, parameterProperties, "Param properties are not as expected");
        }

It tests the requirement:

“The input parameter @departmentID should have the data type – smallint”

Each expected properties for a sproc parameter can be defined in each ‘Row’.

For @departmentID this is set by: [Row(“@departmentID”, “1,IN,smallint,N/A”)]

The variable ‘expectedParameterProperties’ has a value of “1,IN,smallint,N/A” – which breaks down into the following:

• Ordinal position – 1
• Parameter type – hence ‘IN’ (output would be ‘INOUT’)
• Data type – smallint
• String length – smallint therefore ‘N/A’ (examples for ‘string’ data types e.g. ‘varchar,20’ or ‘nvarchar,256’)

Section C

        [RowTest]
        [Row("@departmentID", "HumanResources.Department", "DepartmentID")]
        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");
        }

It tests the requirement:

“The input parameter @departmentID should have the same properties as that of the column – HumanResources.Department.DepartmentID”

DBTestUnit can check that a parameter has the same data type as that of a table/view column.

Each parameter that should be the same as a table/view column is defined in a ‘Row’

For @departmentID this is set by: [Row(“@departmentID”, “HumanResources.Department”, “DepartmentID”)]

What happens when the tests are run?
The image below shows the output from the MBUnit console when the tests are run.

As expected most fail as the sproc does not yet meet the requirements.

Two of the tests pass, as the sproc, in its current form, does meet the requirement of not having any output parameters.

TDD a new sproc - failing unit tests

Making all the tests pass
The next step is to write the implementation script/code to make the tests pass.

Just enough script is written to ensure that the tests pass. Later steps will add further functionality.

A very basic SQL script – as shown below – can be created and run against the database.

IF EXISTS(SELECT ROUTINE_NAME 
	 FROM INFORMATION_SCHEMA.ROUTINES
	 WHERE ROUTINE_TYPE = 'PROCEDURE'
	 AND ROUTINE_SCHEMA + '.' + ROUTINE_NAME = 'HumanResources.uspGetCurrentEmployeesForDepartment')
	
	DROP PROC HumanResources.uspGetCurrentEmployeesForDepartment
GO

CREATE PROC HumanResources.uspGetCurrentEmployeesForDepartment
	@departmentID smallint
AS

SET NOCOUNT ON

GO

The only change from part 1 is the addition of the input parameter ‘@departmentID smallint’

If the tests are run again they will all pass as the sproc now meets the requirements/expectations as specified in the unit tests.

The image below shows the output from the MBUnit console when they are run.

TDD a new sproc - all unit tests pass

How does this work?
The tests outlined in part 1 are all declarative in nature.

The tests above are mixed in nature.

Section A are similar to those in part 1

Those in Section B and Section C include some ‘boiler plate’ test code.

All are similar in the fact that they use methods in DBTestUnit test classes that run queries against the various INFORMATION_SCHEMA views to ensure the actual sproc has the same properties as those defined in the tests.

Section A
The sproc test class – ‘uspGetCurrentEmployeesForDepartment’ inherits from a DBTestUnit abstract class named ‘SprocTestBase’ (part of DBTestUnit.UnitTestBaseClass.MSSQL namespace) – as shown in the code sample below:

namespace AdventureWorks.DatabaseTest.Tests.Sprocs.Schema
{
    [TestFixture]
    public class uspGetCurrentEmployeesForDepartment : SprocTestBase
    {

‘SprocTestBase’ has a number of test methods

e.g. for testing parameters – InputCount(), InputCount_Named(), OutputCount(), OutputCount_Named()

When the tests are run – the ‘SprocTestBase’ test methods are called.

The expected values – as shown below – are used by these methods when comparing against the actual values returned from the database.

            expectedInputCount = 1;
            expectedInputList = "@departmentID";
            expectedOutputCount = 0;
            expectedOutputList = "";

DBTestUnit then runs a number of queries against the database being tested.

For example, to test the expected input parameter count the following query is run:

SELECT COUNT(DATA_TYPE) 
FROM INFORMATION_SCHEMA.PARAMETERS 
WHERE SPECIFIC_SCHEMA = 'HumanResources'  AND SPECIFIC_NAME = 'uspGetCurrentEmployeesForDepartment' 
AND PARAMETER_MODE='IN'

If the count returned is the same as that set in ‘expectedInputCount’ i.e. 1 then the test will pass

To test the names of the input parameters the following query is run:

SELECT COUNT(DATA_TYPE) 
FROM INFORMATION_SCHEMA.PARAMETERS 
WHERE SPECIFIC_SCHEMA = 'HumanResources'  AND SPECIFIC_NAME = 'uspGetCurrentEmployeesForDepartment' 
AND PARAMETER_MODE='IN' 
AND PARAMETER_NAME IN ('@departmentID')

To test the expected output parameters similar queries are run but with – PARAMETER_MODE’ set to ‘INOUT’

Section B
The sproc test class – ‘uspGetCurrentEmployeesForDepartment’ instantiates a DBTestUnit object named ‘Sproc’ (part of DBTestUnit.InfoSchema namespace)

This has a number of test methods as shown below:

      
public string ParameterProperties(string parameterName);
public bool ParameterIsSameAsColumn(string parameterName, string entityFullName, string columnName);

To test the properties of the parameters the following requirement was set:

        [RowTest]
        [Row("@departmentID", "1,IN,smallint,N/A")]
        public void T05_ParameterProperties(string paramName, string expectedParameterProperties)
        {
            string parameterProperties = sproc.ParameterProperties(paramName);
            Assert.AreEqual(expectedParameterProperties, parameterProperties, "Param properties are not as expected");
        }

When the test ‘T05_ParameterProperties’ is run – the test method ‘sproc.ParameterProperties’ is called.

The following query is then run against the database:

SELECT convert(varchar,ordinal_position) + ',' + parameter_mode + ',' + data_type + ',' + COALESCE(convert(varchar,character_maximum_length),'N/A') 
FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_SCHEMA = 'HumanResources'  AND SPECIFIC_NAME = 'uspGetCurrentEmployeesForDepartment' 
AND PARAMETER_NAME = '@departmentID'

If the returned value is the same as the expected value i.e. “1,IN,smallint,N/A” then the test will pass

Section C
To test that the parameter should be the same as a table/view column – the following requirement was set:

        [RowTest]
        [Row("@departmentID", "HumanResources.Department", "DepartmentID")]
        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");
        }

When the test ‘T06_ParameterIsTheSameDataTypeAndLength’ is run – the test method ‘sproc.ParameterIsSameAsColumn’ is called.

The following query is then run against the database:

T06_ParameterIsTheSameDataTypeAndLength

SELECT COUNT(*) 
FROM INFORMATION_SCHEMA.COLUMNS as c, INFORMATION_SCHEMA.PARAMETERS as p 
WHERE c.DATA_TYPE=p.DATA_TYPE AND COALESCE(c.CHARACTER_MAXIMUM_LENGTH,'')=COALESCE(p.CHARACTER_MAXIMUM_LENGTH,'') 
AND c.TABLE_SCHEMA + '.' + c.TABLE_NAME='HumanResources.Department' AND c.COLUMN_NAME='DepartmentID' 
AND p.SPECIFIC_SCHEMA='HumanResources' AND p.SPECIFIC_NAME='uspGetCurrentEmployeesForDepartment' AND p.PARAMETER_NAME='@departmentID'

If @departmentID has the same data type as HumanResources.Department.DepartmentID – then the query above will return a count of 1 and DBTestUnit will return ‘true’ to the calling test method. If the count is 0 – i.e. the data type is not the same – then DBTestUnit will return ‘false’.

Summary
At first, it might seem quite complicated to carry out this type of test. Especially as it is only testing one input parameter.

It is worth remembering that the developer only needs to concentrate on defining the tests/requirements of the new sproc – as per the section Writing a test that defines the requirement/expectations. DBTestUnit is responsible for carrying out the tests – and to a large degree this detail is hidden from the developer.

As mentioned previously, the DBTestUnit download includes a number of sample tests that makes it relatively easy to get started.

If any change is made to the properties of the sproc without a corresponding change to the tests/requirements- e.g. another parameter is added, the existing one is renamed/changed etc – then these tests will quickly identify this. Therefore, this type of test can help to ensure that the DB API offered to clients is maintained whilst any changes to the internal schema are made.

What next
This blog has shown how to take a TDD approach when testing the expected properties of a new sproc.

The next blog in this series will look at the third step from Database unit testing patterns. How to unit test the expected functionality of the new sproc – in this case the data that is outputted.

Unit testing ‘Select’ stored procedures – part 1

Step 1 – testing that a new sproc exists

Intro
This is the first in a series of blogs on how to take a test driven development (TDD) approach when creating stored procedures using DBTestUnit.

It is an approach that I have been using for a number of years and IMHO enables a more agile approach to database development to be taken.

This series will follow the steps outlined in – Database unit testing patterns.

This is the first in the series – so it will look at testing to ensure that the new stored procedure exists.

The complete series

Part 2 – Testing the properties of a sproc

Part 3 – Testing the data outputted by sproc – initial overview

Testing the data outputted by sproc – detailed

Part 4 – Testing permissions on a sproc

What is TDD? A quick overview
Taken from wikipedia.

“first the developer writes a failing automated test case that defines a desired improvement or new function, then produces code to pass that test and finally refactors the new code to acceptable standards.”

Why test that a SQL object exists?
Over the years I have seen many application releases fail due to mistakes when carrying out database deployments. Many development teams have issues in managing database schemas/versioning and knowing what SQL objects should be in a particular build.

Coupled with versioning and source control – this type of test can help prevent these issues from occurring.

Background scenario
For this blog and the rest of the series Microsoft’s AdventureWorks2008R2 sample database will be used.

The requirement is to create a new sproc on an existing database.

For a given department the new sproc should return the details of current employee in that department. Following the naming conventions of the existing database it will be named – HumanResources.uspGetCurrentEmployeesForDepartment.

The expectation to be met could be written as “A stored procedure named HumanResources.uspGetCurrentEmployeesForDepartment should exist in the database”.

Sample C# test templates and SQL helper scripts that are included with DBTestUnit download will be used.

Writing a test that defines the requirement/expectations

So the first thing to do is to write a ‘failing automated test’ to assert that the store procedure exists.

DBTestUnit includes a sample test:

..\DBTemplate\src\DBTemplateDatabaseTest\Tests\DatabaseTest.cs

that can be used as a template to get started.

A copy of this sample code is shown below:

using System;
using MbUnit.Framework;
using DBTestUnit.UnitTestBaseClass.MSSQL;
using DBTestUnit.Util;
using DBTest = DBTestUnit.InfoSchema;
using AdventureWorks.DatabaseTest;

namespace AdventureWorks.DatabaseTest.Tests
{
    [TestFixture]
    public class SQLDatabase : SQLDatabaseTestBase
    {
        public SQLDatabase()
        {
            dbInstance = "AdventureWorks";
  
            sqlDatabase = new DBTest.SQLDatabase(dbInstance);
            tables = new DBTest.Tables(dbInstance);
            views = new DBTest.Views(dbInstance);
            triggers = new DBTest.Triggers(dbInstance);
            sprocs = new DBTest.Sprocs(dbInstance);
            functions = new DBTest.Functions(dbInstance);
            linkedServers = new DBTest.LinkedServers(dbInstance);
            synonyms = new DBTest.Synonyms(dbInstance);

            expectedDBAnsiNullsEnabled = "true";
            expectedDBAnsiNullSetting = "false";
            expectedDBCollation = "Latin1_General_CI_AS";
            expectedDBConcatNullsYieldsNulls = "true";

            expectedTableCount = 71;
            expectedTableList = "Person.Address,Person.AddressType,dbo.AWBuildVersion,Production.BillOfMaterials,Person.BusinessEntity,Person.BusinessEntityAddress,Person.BusinessEntityContact,Person.ContactType,Person.CountryRegion,Sales.CountryRegionCurrency,Sales.CreditCard,Production.Culture,Sales.Currency,Sales.CurrencyRate,Sales.Customer,dbo.DatabaseLog,HumanResources.Department,Production.Document,Person.EmailAddress,HumanResources.Employee,HumanResources.EmployeeDepartmentHistory,HumanResources.EmployeePayHistory,dbo.ErrorLog,Production.Illustration,HumanResources.JobCandidate,Production.Location,Person.Password,Person.Person,Sales.PersonCreditCard,Person.PersonPhone,Person.PhoneNumberType,Production.Product,Production.ProductCategory,Production.ProductCostHistory,Production.ProductDescription,Production.ProductDocument,Production.ProductInventory,Production.ProductListPriceHistory,Production.ProductModel,Production.ProductModelIllustration,Production.ProductModelProductDescriptionCulture,Production.ProductPhoto,Production.ProductProductPhoto,Production.ProductReview,Production.ProductSubcategory,Purchasing.ProductVendor,Purchasing.PurchaseOrderDetail,Purchasing.PurchaseOrderHeader,Sales.SalesOrderDetail,Sales.SalesOrderHeader,Sales.SalesOrderHeaderSalesReason,Sales.SalesPerson,Sales.SalesPersonQuotaHistory,Sales.SalesReason,Sales.SalesTaxRate,Sales.SalesTerritory,Sales.SalesTerritoryHistory,Production.ScrapReason,HumanResources.Shift,Purchasing.ShipMethod,Sales.ShoppingCartItem,Sales.SpecialOffer,Sales.SpecialOfferProduct,Person.StateProvince,Sales.Store,Production.TransactionHistory,Production.TransactionHistoryArchive,Production.UnitMeasure,Purchasing.Vendor,Production.WorkOrder,Production.WorkOrderRouting";
            expectedViewCount = 20;
            expectedViewList = "Person.vAdditionalContactInfo,HumanResources.vEmployee,HumanResources.vEmployeeDepartment,HumanResources.vEmployeeDepartmentHistory,Sales.vIndividualCustomer,HumanResources.vJobCandidate,HumanResources.vJobCandidateEducation,HumanResources.vJobCandidateEmployment,Sales.vPersonDemographics,Production.vProductAndDescription,Production.vProductModelCatalogDescription,Production.vProductModelInstructions,Sales.vSalesPerson,Sales.vSalesPersonSalesByFiscalYears,Person.vStateProvinceCountryRegion,Sales.vStoreWithAddresses,Sales.vStoreWithContacts,Sales.vStoreWithDemographics,Purchasing.vVendorWithAddresses,Purchasing.vVendorWithContacts";
            expectedSprocCount = 10;
            expectedSprocList = "dbo.uspGetBillOfMaterials,dbo.uspGetEmployeeManagers,dbo.uspGetManagerEmployees,dbo.uspGetWhereUsedProductID,dbo.uspLogError,dbo.uspPrintError,dbo.uspSearchCandidateResumes,HumanResources.uspUpdateEmployeeHireInfo,HumanResources.uspUpdateEmployeeLogin,HumanResources.uspUpdateEmployeePersonalInfo";
            expectedFunctionCount = 11;
            expectedFunctionList = "dbo.ufnGetAccountingEndDate,dbo.ufnGetAccountingStartDate,dbo.ufnGetContactInformation,dbo.ufnGetDocumentStatusText,dbo.ufnGetProductDealerPrice,dbo.ufnGetProductListPrice,dbo.ufnGetProductStandardCost,dbo.ufnGetPurchaseOrderStatusText,dbo.ufnGetSalesOrderStatusText,dbo.ufnGetStock,dbo.ufnLeadingZeros";

            expectedDMLTriggerList = "dEmployee,dVendor,iduSalesOrderDetail,iPurchaseOrderDetail,iuPerson,iWorkOrder,uPurchaseOrderDetail,uPurchaseOrderHeader,uSalesOrderHeader,uWorkOrder";
            expectedDMLTriggerCount = 10;
            expectedDDLTriggerList = "ddlDatabaseTriggerLog";
            expectedDDLTriggerCount = 1;
            expectedTriggerCount = 11;
            expectedTriggerList = expectedDMLTriggerList + "," + expectedDDLTriggerList;

            expectedLinkedServerCount = 0;
            expectedLinkedServerList = "";

            expectedSynonymCount = 0;
            expectedSynonymList = "";
        }

This lists all of the SQL objects – tables, views, sprocs etc – that are expected to be in the AdventureWorks database. When these tests are run – if the database being tested does contain the expected SQL objects – then they will all pass. See a previous blog Do you know what’s in your database? for more details on how to do this.

For testing sprocs there are two key variables:

            expectedSprocCount = 10;
            expectedSprocList = "dbo.uspGetBillOfMaterials,dbo.uspGetEmployeeManagers,dbo.uspGetManagerEmployees,dbo.uspGetWhereUsedProductID,dbo.uspLogError,dbo.uspPrintError,dbo.uspSearchCandidateResumes,HumanResources.uspUpdateEmployeeHireInfo,HumanResources.uspUpdateEmployeeLogin,HumanResources.uspUpdateEmployeePersonalInfo";

If the sproc count returned from the database being tested is not the same as the value in ‘expectedSprocCount’ ie 10 – the test will fail. A test will also fail if the database does not contain the sprocs named in ‘expectedSprocList’. The two tests combined ensure that the database contains all of the expected sprocs.

So, taking a TDD approach on creating a new sproc:

• The sproc count will need to be increased by 1 ie to 11.
• The new sproc name will need to be added to the list.

The code above should be amended as follows:

		expectedSprocCount = 11;
            	expectedSprocList = "HumanResources.uspGetCurrentEmployeesForDepartment,dbo.uspGetBillOfMaterials,dbo.uspGetEmployeeManagers,dbo.uspGetManagerEmployees,dbo.uspGetWhereUsedProductID,dbo.uspLogError,dbo.uspPrintError,dbo.uspSearchCandidateResumes,HumanResources.uspUpdateEmployeeHireInfo,HumanResources.uspUpdateEmployeeLogin,HumanResources.uspUpdateEmployeePersonalInfo";

If the updated tests are run again – the two tests that check for sprocs using the values set in ‘expectedSprocCount’ and ‘expectedSprocList’ will, as expectedfail – as the new sproc has not been created yet.

Making the tests pass

The next step is to write the implementation script/code to make the tests pass. At this point only enough code is written so that the tests pass (later steps will actually implement further functionality.)

So, a very basic SQL script – similar to that shown below – is created and run against the database.

CREATE PROC HumanResources.uspGetCurrentEmployeesForDepartment
AS

SET NOCOUNT ON

GO

When the tests are run again they will now all pass as the sproc has been created and the database now meets requirements/expectations as specified in the unit tests.

If the new sproc is not deployed into an environment – or if someone deletes/renames it by mistake – then these tests can quickly identify this.

How does this work?
One of the objectives of DBTestUnit is to minimise the amount of boiler plate test code ‘testers’ have to write and to make the tests as declarative as possible – see Overview of what DBTestUnit does and how it work for more details.

In the sample test class – ‘SQLDatabase’ – shown above, as we have seen, the requirements are expressed by setting values in variables eg expectedSprocCount = 11. This test class inherits from a DBTestUnit abstract class named ‘SQLDatabaseTestBase’.

As shown in the following code segment:

    [TestFixture]
    public class SQLDatabase : SQLDatabaseTestBase

‘SQLDatabaseTestBase’ contains the test methods – the ‘boiler plate’ test code – that actually tests the counts of different types of SQL objects in the database.

When the tests are run – these methods are called, which cause DBTestUnit to run a number of queries against the various INFORMATION_SCHEMA views/internal systems tables of the database being tested*.

For example, to test the sproc count the following SQL query is run**:

SELECT COUNT(ROUTINE_NAME) 
FROM INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_TYPE='PROCEDURE' 
 AND ROUTINE_NAME NOT LIKE 'dt_%'

The value returned by this query – the actual sproc count – is then compared to the value set in ‘expectedSprocCount’. If different the test fails.

To check that the actually sproc name exists it runs the following query:

SELECT COUNT(ROUTINE_NAME) 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_TYPE='PROCEDURE' 
AND ROUTINE_NAME NOT LIKE 'dt_%' 
AND SPECIFIC_SCHEMA + '.' + ROUTINE_NAME IN ('HumanResources.uspGetCurrentEmployeesForDepartment','dbo.uspGetBillOfMaterials','dbo.uspGetEmployeeManagers','dbo.uspGetManagerEmployees','dbo.uspGetWhereUsedProductID','dbo.uspLogError','dbo.uspPrintError','dbo.uspSearchCandidateResumes','HumanResources.uspUpdateEmployeeHireInfo','HumanResources.uspUpdateEmployeeLogin','HumanResources.uspUpdateEmployeePersonalInfo')

In this case the value set in ‘expectedSprocList’ is used by the query. If the count returned is the same as that in ‘expectedSprocCount’ then the test passes.

A similar type of approach is used for other SQL objects such as tables, views and functions.

Having as much ‘boiler plate’ test code in the DBTestUnit abstract class as possible allows the tester to focus on the database requirements – the WHAT – whilst DBTestUnit manages the HOW – to actually carry out the test.

* DBTestUnit has implements different queries depending on the type of DBMS eg MS SQL, MySQL or Oracle.

** To view the actually queries DBTestUnit implements run SQL profiler against your database as the unit tests are run.

Advantages of this approach

• SQL object existence is explicitly tested. If the SQL object has not be deployed, if it is deleted or renamed – then this type of test will quickly identify this.
• Tests can be reused as deployment tests. The tests are built as part of the initial development process but can be reused to ensure different environments have the correct database schema.
• Tests can act as documentation as they list all of the expected SQL objects.
• Can help save time when running automated tests. These can be run first before running the complete suite of automated database tests. If any of the initial tests fail – ie an expected SQL object is not present – then the rest of the more ‘longer running’ tests do not need to be run until the schema issues are resolved.

Disadvantages of approach
More development time will be required to set these tests up.

What next
This blog has shown how to complete the first step in taking a TDD approach when developing a new stored procedure exists using DBTestUnit.

The next blog in this series will look at the second step – taken from Database unit testing patterns – testing the properties/schema of the new stored procedure.