Unit testing ‘Select’ stored procedures – part 2

2011/03/15

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

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

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

2011/03/01

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.