Unit testing is an essential part of software development, and it is equally important for database development. In this article, we will explore the concept of unit testing for stored procedures in SQL Server.

One of the most powerful tools for unit testing in SQL Server is Visual Studio Team Edition for Database Professionals (DBPro). DBPro provides an extensible testing environment that allows you to write your own test conditions. This flexibility is particularly useful when you need to compare data before and after making changes to a stored procedure.

Let’s say you have a stored procedure that you want to optimize by changing the join criteria or adding common table expressions. You need to ensure that these changes do not affect the results of the procedure. This is where the data comparison test comes in handy. By comparing the data, data types, column order, and sort order before and after the changes, you can ensure that the procedure is still producing the same results.

Unfortunately, the data comparison test is not available out of the box in DBPro. However, you can easily add it yourself. In this article, we will walk through the steps to create a custom test condition for data comparison.

Before we dive into the implementation details, let’s briefly discuss the principles of Agile development and test-driven development. Agile development emphasizes iterative development and continuous testing. Test-driven development, as described by Scott Ambler, is a key practice in Agile development. Microsoft has embraced these principles internally and documented different approaches to Agile development. It is worth exploring these approaches to gain a deeper understanding of Agile development methodologies.

Assuming you have a working knowledge of TSQL, C#, and Visual Studio, let’s get started with the implementation. We will be using the Microsoft.VisualStudio.TeamSystem.Data.UnitTesting namespace, so make sure you have the necessary references added to your project.

The core of our custom test condition is the Assert function, which is responsible for comparing the data before and after the changes. We start by verifying that the batch exists and that the batch returned result sets. If any of these conditions are not met, we throw an error using the AssertFailedException class.

Next, we retrieve the result sets from the ExecutionResult object and create DataTables to hold the data. We then compare the number of rows in each set and check the column data types. Finally, we compare the actual data in each row to ensure they match.

Once we have implemented the custom test condition, we need to integrate it into DBPro. This involves installing the DLL and creating an XML file to register the test condition with Visual Studio. After restarting Visual Studio, you will be able to use the custom test condition in your tests.

Using the custom test condition is straightforward. Simply run two queries in your test and specify the main result set and the compare result set using the properties of the test condition. Make sure the initial data in both result sets is the same, including the row and column order. If the data differs, the test will fail.

In conclusion, unit testing is crucial for ensuring the quality and reliability of your stored procedures in SQL Server. With the extensibility of DBPro, you can easily create custom test conditions, such as the data comparison test, to validate the results of your procedures. By following the principles of Agile development and test-driven development, you can improve the efficiency and effectiveness of your database development process.