Has anyone had any success in unit testing SQL stored procedures?

We’ve found that the unit tests we’ve written for our C#/C++ code have really paid off. But we still have thousands of lines of business logic in stored procedures, which only really get tested in anger when our product is rolled out to a large number of users.

What makes this worse is that some of these stored procedures end up being very long, because of the performance hit when passing temporary tables between SPs. This has prevented us from refactoring to make the code simpler.

We have made several attempts at building unit tests around some of our key stored procedures (primarily testing the performance), but have found that setting up the test data for these tests is really hard. For example, we end up copying around test databases. In addition to this, the tests end up being really sensitive to change, and even the smallest change to a stored proc. or table requires a large amount of changes to the tests. So after many builds breaking due to these database tests failing intermittently, we’ve just had to pull them out of the build process.

So, the main part of my questions is: has anyone ever successfully written unit tests for their stored procedures?

The second part of my questions is whether unit testing would be/is easier with linq?

I was thinking that rather than having to set up tables of test data, you could simply create a collection of test objects, and test your linq code in a “linq to objects” situation? (I am a totally new to linq so don’t know if this would even work at all)

15.08.2008 15:29:14

I ran into this same issue a while back and found that if I created a simple abstract base class for data access that allowed me to inject a connection and transaction, I could unit test my sprocs to see if they did the work in SQL that I asked them to do and then rollback so none of the test data is left in the db.

This felt better than the usual "run a script to setup my test db, then after the tests run do a cleanup of the junk/test data". This also felt closer to unit testing because these tests could be run alone w/out having a great deal of "everything in the db needs to be 'just so' before I run these tests".

Here is a snippet of the abstract base class used for data access

Public MustInherit Class Repository(Of T As Class)
    Implements IRepository(Of T)

    Private mConnectionString As String = ConfigurationManager.ConnectionStrings("Northwind.ConnectionString").ConnectionString
    Private mConnection As IDbConnection
    Private mTransaction As IDbTransaction

    Public Sub New()
        mConnection = Nothing
        mTransaction = Nothing
    End Sub

    Public Sub New(ByVal connection As IDbConnection, ByVal transaction As IDbTransaction)
        mConnection = connection
        mTransaction = transaction
    End Sub

    Public MustOverride Function BuildEntity(ByVal cmd As SqlCommand) As List(Of T)

    Public Function ExecuteReader(ByVal Parameter As Parameter) As List(Of T) Implements IRepository(Of T).ExecuteReader
        Dim entityList As List(Of T)
        If Not mConnection Is Nothing Then
            Using cmd As SqlCommand = mConnection.CreateCommand()
                cmd.Transaction = mTransaction
                cmd.CommandType = Parameter.Type
                cmd.CommandText = Parameter.Text
                If Not Parameter.Items Is Nothing Then
                    For Each param As SqlParameter In Parameter.Items
                End If
                entityList = BuildEntity(cmd)
                If Not entityList Is Nothing Then
                    Return entityList
                End If
            End Using
            Using conn As SqlConnection = New SqlConnection(mConnectionString)
                Using cmd As SqlCommand = conn.CreateCommand()
                    cmd.CommandType = Parameter.Type
                    cmd.CommandText = Parameter.Text
                    If Not Parameter.Items Is Nothing Then
                        For Each param As SqlParameter In Parameter.Items
                    End If
                    entityList = BuildEntity(cmd)
                    If Not entityList Is Nothing Then
                        Return entityList
                    End If
                End Using
            End Using
        End If

        Return Nothing
    End Function
End Class

next you will see a sample data access class using the above base to get a list of products

Public Class ProductRepository
    Inherits Repository(Of Product)
    Implements IProductRepository

    Private mCache As IHttpCache

    'This const is what you will use in your app
    Public Sub New(ByVal cache As IHttpCache)
        mCache = cache
    End Sub

    'This const is only used for testing so we can inject a connectin/transaction and have them roll'd back after the test
    Public Sub New(ByVal cache As IHttpCache, ByVal connection As IDbConnection, ByVal transaction As IDbTransaction)
        MyBase.New(connection, transaction)
        mCache = cache
    End Sub

    Public Function GetProducts() As System.Collections.Generic.List(Of Product) Implements IProductRepository.GetProducts
        Dim Parameter As New Parameter()
        Parameter.Type = CommandType.StoredProcedure
        Parameter.Text = "spGetProducts"
        Dim productList As List(Of Product)
        productList = MyBase.ExecuteReader(Parameter)
        Return productList
    End Function

    'This function is used in each class that inherits from the base data access class so we can keep all the boring left-right mapping code in 1 place per object
    Public Overrides Function BuildEntity(ByVal cmd As System.Data.SqlClient.SqlCommand) As System.Collections.Generic.List(Of Product)
        Dim productList As New List(Of Product)
        Using reader As SqlDataReader = cmd.ExecuteReader()
            Dim product As Product
            While reader.Read()
                product = New Product()
                product.ID = reader("ProductID")
                product.SupplierID = reader("SupplierID")
                product.CategoryID = reader("CategoryID")
                product.ProductName = reader("ProductName")
                product.QuantityPerUnit = reader("QuantityPerUnit")
                product.UnitPrice = reader("UnitPrice")
                product.UnitsInStock = reader("UnitsInStock")
                product.UnitsOnOrder = reader("UnitsOnOrder")
                product.ReorderLevel = reader("ReorderLevel")
            End While
            If productList.Count > 0 Then
                Return productList
            End If
        End Using
        Return Nothing
    End Function
End Class

And now in your unit test you can also inherit from a very simple base class that does your setup / rollback work - or keep this on a per unit test basis

below is the simple testing base class I used

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.VisualStudio.TestTools.UnitTesting

Public MustInherit Class TransactionFixture
    Protected mConnection As IDbConnection
    Protected mTransaction As IDbTransaction
    Private mConnectionString As String = ConfigurationManager.ConnectionStrings("Northwind.ConnectionString").ConnectionString

    <TestInitialize()> _
    Public Sub CreateConnectionAndBeginTran()
        mConnection = New SqlConnection(mConnectionString)
        mTransaction = mConnection.BeginTransaction()
    End Sub

    <TestCleanup()> _
    Public Sub RollbackTranAndCloseConnection()
    End Sub
End Class

and finally - the below is a simple test using that test base class that shows how to test the entire CRUD cycle to make sure all the sprocs do their job and that your ado.net code does the left-right mapping correctly

I know this doesn't test the "spGetProducts" sproc used in the above data access sample, but you should see the power behind this approach to unit testing sprocs

Imports SampleApplication.Library
Imports System.Collections.Generic
Imports Microsoft.VisualStudio.TestTools.UnitTesting

<TestClass()> _
Public Class ProductRepositoryUnitTest
    Inherits TransactionFixture

    Private mRepository As ProductRepository

    <TestMethod()> _
    Public Sub Should-Insert-Update-And-Delete-Product()
        mRepository = New ProductRepository(New HttpCache(), mConnection, mTransaction)
        '** Create a test product to manipulate throughout **'
        Dim Product As New Product()
        Product.ProductName = "TestProduct"
        Product.SupplierID = 1
        Product.CategoryID = 2
        Product.QuantityPerUnit = "10 boxes of stuff"
        Product.UnitPrice = 14.95
        Product.UnitsInStock = 22
        Product.UnitsOnOrder = 19
        Product.ReorderLevel = 12
        '** Insert the new product object into SQL using your insert sproc **'
        '** Select the product object that was just inserted and verify it does exist **'
        '** Using your GetProductById sproc **'
        Dim Product2 As Product = mRepository.GetProduct(Product.ID)
        Assert.AreEqual("TestProduct", Product2.ProductName)
        Assert.AreEqual(1, Product2.SupplierID)
        Assert.AreEqual(2, Product2.CategoryID)
        Assert.AreEqual("10 boxes of stuff", Product2.QuantityPerUnit)
        Assert.AreEqual(14.95, Product2.UnitPrice)
        Assert.AreEqual(22, Product2.UnitsInStock)
        Assert.AreEqual(19, Product2.UnitsOnOrder)
        Assert.AreEqual(12, Product2.ReorderLevel)
        '** Update the product object **'
        Product2.ProductName = "UpdatedTestProduct"
        Product2.SupplierID = 2
        Product2.CategoryID = 1
        Product2.QuantityPerUnit = "a box of stuff"
        Product2.UnitPrice = 16.95
        Product2.UnitsInStock = 10
        Product2.UnitsOnOrder = 20
        Product2.ReorderLevel = 8
        mRepository.UpdateProduct(Product2) '**using your update sproc
        '** Select the product object that was just updated to verify it completed **'
        Dim Product3 As Product = mRepository.GetProduct(Product2.ID)
        Assert.AreEqual("UpdatedTestProduct", Product2.ProductName)
        Assert.AreEqual(2, Product2.SupplierID)
        Assert.AreEqual(1, Product2.CategoryID)
        Assert.AreEqual("a box of stuff", Product2.QuantityPerUnit)
        Assert.AreEqual(16.95, Product2.UnitPrice)
        Assert.AreEqual(10, Product2.UnitsInStock)
        Assert.AreEqual(20, Product2.UnitsOnOrder)
        Assert.AreEqual(8, Product2.ReorderLevel)
        '** Delete the product and verify it does not exist **'
        '** The above will use your delete product by id sproc **'
        Dim Product4 As Product = mRepository.GetProduct(Product3.ID)
        Assert.AreEqual(Nothing, Product4)
    End Sub

End Class

I know this is a long example, but it helped to have a reusable class for the data access work, and yet another reusable class for my testing so I didn't have to do the setup/teardown work over and over again ;)

26.08.2008 12:12:58

Have you tried DBUnit? It's designed to unit test your database, and just your database, without needing to go through your C# code.

15.08.2008 15:35:02

I am assuming that you want unit testing in MSSQL. Looking at DBUnit there are some limitations in it's support for MSSQL. It doesn't support NVarChar for instance. Here are some real users and their problems with DBUnit.

15.08.2008 15:40:50

LINQ will simplify this only if you remove the logic from your stored procedures and reimplement it as linq queries. Which would be much more robust and easier to test, definitely. However, it sounds like your requirements would preclude this.

TL;DR: Your design has issues.

15.08.2008 15:44:00

If you think about the kind of code that unit testing tends to promote: small highly-cohesive and lowly-coupled routines, then you should pretty much be able to see where at least part of the problem might be.

In my cynical world, stored procedures are part of the RDBMS world's long-standing attempt to persuade you to move your business processing into the database, which makes sense when you consider that server license costs tend to be related to things like processor count. The more stuff you run inside your database, the more they make from you.

But I get the impression you're actually more concerned with performance, which isn't really the preserve of unit testing at all. Unit tests are supposed to be fairly atomic and are intended to check behaviour rather than performance. And in that case you're almost certainly going to need production-class loads in order to check query plans.

I think you need a different class of testing environment. I'd suggest a copy of production as the simplest, assuming security isn't an issue. Then for each candidate release, you start with the previous version, migrate using your release procedures (which will give those a good testing as a side-effect) and run your timings.

Something like that.

15.08.2008 15:51:15

We unit test the C# code that calls the SPs.
We have build scripts, creating clean test databases.
And bigger ones we attach and detach during test fixture.
These tests could take hours, but I think it`s worth it.

15.08.2008 15:51:21

We use DataFresh to rollback changes between each test, then testing sprocs is relatively easy.

What is still lacking is code coverage tools.

15.08.2008 16:41:34

But I get the impression you're actually more concerned with performance, which isn't really the preserve of unit testing at all. Unit tests are supposed to be fairly atomic and are intended to check behaviour rather than performance. And in that case you're almost certainly going to need production-class loads in order to check query plans.

I think there are two quite distinct testing areas here: the performance, and the actual logic of the stored procedures.

I gave the example of testing the db performance in the past and, thankfully, we have reached a point where the performance is good enough.

I completely agree that the situation with all the business logic in the database is a bad one, but it's something that we've inherited from before most of our developers joined the company.

However, we're now adopting the web services model for our new features, and we've been trying to avoid stored procedures as much as possible, keeping the logic in the C# code and firing SQLCommands at the database (although linq would now be the preferred method). There is still some use of the existing SPs which was why I was thinking about retrospectively unit testing them.

15.08.2008 16:53:22

One option to re-factor the code (I'll admit a ugly hack) would be to generate it via CPP (the C preprocessor) M4 (never tried it) or the like. I have a project that is doing just that and it is actually mostly workable.

The only case I think that might be valid for is 1) as an alternative to KLOC+ stored procedures and 2) and this is my cases, when the point of the project is to see how far (into insane) you can push a technology.

15.08.2008 17:09:23

The key to testing stored procedures is writing a script that populates a blank database with data that is planned out in advance to result in consistent behavior when the stored procedures are called.

I have to put my vote in for heavily favoring stored procedures and placing your business logic where I (and most DBAs) think it belongs, in the database.

I know that we as software engineers want beautifully refactored code, written in our favorite language, to contain all of our important logic, but the realities of performance in high volume systems, and the critical nature of data integrity, require us to make some compromises. Sql code can be ugly, repetitive, and hard to test, but I can't imagine the difficulty of tuning a database without having complete control over the design of the queries.

I am often forced to completely redesign queries, to include changes to the data model, to get things to run in an acceptable amount of time. With stored procedures, I can assure that the changes will be transparent to the caller, since a stored procedure provides such excellent encapsulation.

15.08.2008 17:27:51
You were trolling with your snswer, wern't you? Is business logic used in an overloaded sense? So how would you unit test your logic, with integration or higher stack tests? I have had far more success keeping sprocs simple and doing logical lifting in code. I have had horrible experiences with ridiculously nested sprocs and functions in order to ensure all business logic stayed in DB. Not for me thanks.
brumScouse 16.01.2018 07:54:47

Oh, boy. sprocs don't lend themselves to (automated) unit testing. I sort-of "unit test" my complex sprocs by writing tests in t-sql batch files and hand checking the output of the print statements and the results.

15.08.2008 19:35:27

The problem with unit testing any kind of data-related programming is that you have to have a reliable set of test data to start with. A lot also depends on the complexity of the stored proc and what it does. It would be very hard to automate unit testing for a very complex procedure that modified many tables.

Some of the other posters have noted some simple ways to automate manually testing them, and also some tools you can use with SQL Server. On the Oracle side, PL/SQL guru Steven Feuerstein worked on a free unit testing tool for PL/SQL stored procedures called utPLSQL.

However, he dropped that effort and then went commercial with Quest's Code Tester for PL/SQL. Quest offers a free downloadable trial version. I'm on the verge of trying it out; my understanding is that it is good at taking care of the overhead in setting up a testing framework so that you can focus on just the tests themselves, and it keeps the tests so you can reuse them in regression testing, one of the great benefits of test-driven-development. In addition, it is supposed to be good at more than just checking an output variable and does have provision for validating data changes, but I still have to take a closer look myself. I thought this info might be of value for Oracle users.

18.08.2008 00:07:13

You can also try Visual Studio for Database Professionals. It's mainly about change management but also has tools for generating test data and unit tests.

It's pretty expensive tho.

18.08.2008 09:41:19

I'm in the exact same situation as the original poster. It comes down to performance versus testability. My bias is towards testability (make it work, make it right, make it fast), which suggests keeping business logic out of the database. Databases not only lack the testing frameworks, code factoring constructs, and code analysis and navigation tools found in languages like Java, but highly factored database code is also slow (where highly factored Java code is not).

However, I do recognize the power of database set processing. When used appropriately, SQL can do some incredibly powerful stuff with very little code. So, I'm ok with some set-based logic living in the database even though I will still do everything I can to unit test it.

On a related note, it seems that very long and procedural database code is often a symptom of something else, and I think such code can be converted to testable code without incurring a performance hit. The theory is that such code often represents batch processes that periodically process large amounts of data. If these batch processes were to be converted into smaller chunks of real-time business logic that runs whenever the input data is changed, this logic could be run on the middle-tier (where it can be tested) without taking a performance hit (since the work is done in small chunks in real-time). As a side-effect, this also eliminates the long feedback-loops of batch process error handling. Of course this approach won't work in all cases, but it may work in some. Also, if there is tons of such untestable batch processing database code in your system, the road to salvation may be long and arduous. YMMV.

2.10.2008 06:08:33

Good question.

I have similar problems, and I have taken the path of least resistance (for me, anyway).

There are a bunch of other solutions, which others have mentionned. Many of them are better / more pure / more appropriate for others.

I was already using Testdriven.NET/MbUnit to test my C#, so I simply added tests to each project to call the stored procedures used by that app.

I know, I know. This sounds terrible, but what I need is to get off the ground with some testing, and go from there. This approach means that although my coverage is low I am testing some stored procs at the same time as I am testing the code which will be calling them. There is some logic to this.

2.10.2008 11:40:52

I do poor man's unit testing. If I'm lazy, the test is just a couple of valid invocations with potentially problematic parameter values.


Declare @foo int Set @foo = (Select top 1 foo from mytable)

execute wish_I_had_more_Tests @foo

--look at rowcounts/look for errors
If @@rowcount=1 Print 'Ok!' Else Print 'Nokay!'

Delete from mytable where foo = @foo
create procedure wish_I_had_more_Tests
29.04.2009 03:32:32