<p>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. </p>

<p>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.</p>

<p>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.</p>

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

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

<p>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)</p>

[{'answer_id': 12387, 'author': 'Jon Limjap', 'author_id': 372, 'author_profile': 'https://Stackoverflow.com/users/372', 'pm_score': 3, 'selected': False, 'text': '<p>Have you tried <a href="http://dbunit.sourceforge.net/" rel="nofollow noreferrer">DBUnit</a>? It\'s designed to unit test your database, and just your database, without needing to go through your C# code.</p>\n'}, {'answer_id': 12395, 'author': 'RedWolves', 'author_id': 648, 'author_profile': 'https://Stackoverflow.com/users/648', 'pm_score': 2, 'selected': False, 'text': '<p>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.  <a href="http://dbunit.wikidot.com/mssql" rel="nofollow noreferrer">Here are some real users and their problems with DBUnit.</a></p>\n'}, {'answer_id': 12399, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>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.</p>\n\n<p>TL;DR:  Your design has issues.  </p>\n'}, {'answer_id': 12409, 'author': 'Mike Woodhouse', 'author_id': 1060, 'author_profile': 'https://Stackoverflow.com/users/1060', 'pm_score': 3, 'selected': False, 'text': "<p>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.</p>\n\n<p>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.</p>\n\n<p>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.</p>\n\n<p>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.</p>\n\n<p>Something like that.</p>\n"}, {'answer_id': 12410, 'author': 'John Smithers', 'author_id': 1069, 'author_profile': 'https://Stackoverflow.com/users/1069', 'pm_score': 0, 'selected': False, 'text': '<p>We unit test the C# code that calls the SPs.<br>\nWe have build scripts, creating clean test databases.<br>\nAnd bigger ones we attach and detach during test fixture.<br>\nThese tests could take hours, but I think it`s worth it.</p>\n'}, {'answer_id': 12457, 'author': 'Keith', 'author_id': 905, 'author_profile': 'https://Stackoverflow.com/users/905', 'pm_score': 1, 'selected': False, 'text': '<p>We use <a href="http://blogs.ent0.com/files/default.aspx" rel="nofollow noreferrer">DataFresh</a> to rollback changes between each test, then testing sprocs is relatively easy.</p>\n\n<p>What is still lacking is code coverage tools.</p>\n'}, {'answer_id': 12472, 'author': 'John Sibly', 'author_id': 1078, 'author_profile': 'https://Stackoverflow.com/users/1078', 'pm_score': 2, 'selected': False, 'text': "<blockquote>\n  <p>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.</p>\n</blockquote>\n\n<p>I think there are two quite distinct testing areas here: the performance, and the actual logic of the stored procedures. </p>\n\n<p>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. </p>\n\n<p>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. </p>\n\n<p>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. </p>\n"}, {'answer_id': 12483, 'author': 'BCS', 'author_id': 1343, 'author_profile': 'https://Stackoverflow.com/users/1343', 'pm_score': 0, 'selected': False, 'text': "<p>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.</p>\n\n<p>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. </p>\n"}, {'answer_id': 12508, 'author': 'Eric Z Beard', 'author_id': 1219, 'author_profile': 'https://Stackoverflow.com/users/1219', 'pm_score': 3, 'selected': False, 'text': "<p>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.</p>\n\n<p>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.</p>\n\n<p>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.</p>\n\n<p>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.</p>\n"}, {'answer_id': 12639, 'author': 'Booji Boy', 'author_id': 1433, 'author_profile': 'https://Stackoverflow.com/users/1433', 'pm_score': 0, 'selected': False, 'text': '<p>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.      </p>\n'}, {'answer_id': 14033, 'author': 'Bernard Dy', 'author_id': 1577, 'author_profile': 'https://Stackoverflow.com/users/1577', 'pm_score': 0, 'selected': False, 'text': "<p>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.</p>\n\n<p>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. </p>\n\n<p>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.</p>\n"}, {'answer_id': 14390, 'author': 'Craig', 'author_id': 1073, 'author_profile': 'https://Stackoverflow.com/users/1073', 'pm_score': 2, 'selected': False, 'text': '<p>You can also try <a href="http://msdn.microsoft.com/en-gb/vsts2008/products/bb933747.aspx" rel="nofollow noreferrer">Visual Studio for Database Professionals</a>.  It\'s mainly about change management but also has tools for generating test data and unit tests.  </p>\n\n<p>It\'s pretty expensive tho.</p>\n'}, {'answer_id': 25204, 'author': 'Toran Billups', 'author_id': 2701, 'author_profile': 'https://Stackoverflow.com/users/2701', 'pm_score': 5, 'selected': True, 'text': '<p>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.  </p>\n\n<p>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".</p>\n\n<p><strong>Here is a snippet of the abstract base class used for data access</strong></p>\n\n<pre><code>Public MustInherit Class Repository(Of T As Class)\n    Implements IRepository(Of T)\n\n    Private mConnectionString As String = ConfigurationManager.ConnectionStrings("Northwind.ConnectionString").ConnectionString\n    Private mConnection As IDbConnection\n    Private mTransaction As IDbTransaction\n\n    Public Sub New()\n        mConnection = Nothing\n        mTransaction = Nothing\n    End Sub\n\n    Public Sub New(ByVal connection As IDbConnection, ByVal transaction As IDbTransaction)\n        mConnection = connection\n        mTransaction = transaction\n    End Sub\n\n    Public MustOverride Function BuildEntity(ByVal cmd As SqlCommand) As List(Of T)\n\n    Public Function ExecuteReader(ByVal Parameter As Parameter) As List(Of T) Implements IRepository(Of T).ExecuteReader\n        Dim entityList As List(Of T)\n        If Not mConnection Is Nothing Then\n            Using cmd As SqlCommand = mConnection.CreateCommand()\n                cmd.Transaction = mTransaction\n                cmd.CommandType = Parameter.Type\n                cmd.CommandText = Parameter.Text\n                If Not Parameter.Items Is Nothing Then\n                    For Each param As SqlParameter In Parameter.Items\n                        cmd.Parameters.Add(param)\n                    Next\n                End If\n                entityList = BuildEntity(cmd)\n                If Not entityList Is Nothing Then\n                    Return entityList\n                End If\n            End Using\n        Else\n            Using conn As SqlConnection = New SqlConnection(mConnectionString)\n                Using cmd As SqlCommand = conn.CreateCommand()\n                    cmd.CommandType = Parameter.Type\n                    cmd.CommandText = Parameter.Text\n                    If Not Parameter.Items Is Nothing Then\n                        For Each param As SqlParameter In Parameter.Items\n                            cmd.Parameters.Add(param)\n                        Next\n                    End If\n                    conn.Open()\n                    entityList = BuildEntity(cmd)\n                    If Not entityList Is Nothing Then\n                        Return entityList\n                    End If\n                End Using\n            End Using\n        End If\n\n        Return Nothing\n    End Function\nEnd Class\n</code></pre>\n\n<p><strong>next you will see a sample data access class using the above base to get a list of products</strong></p>\n\n<pre><code>Public Class ProductRepository\n    Inherits Repository(Of Product)\n    Implements IProductRepository\n\n    Private mCache As IHttpCache\n\n    \'This const is what you will use in your app\n    Public Sub New(ByVal cache As IHttpCache)\n        MyBase.New()\n        mCache = cache\n    End Sub\n\n    \'This const is only used for testing so we can inject a connectin/transaction and have them roll\'d back after the test\n    Public Sub New(ByVal cache As IHttpCache, ByVal connection As IDbConnection, ByVal transaction As IDbTransaction)\n        MyBase.New(connection, transaction)\n        mCache = cache\n    End Sub\n\n    Public Function GetProducts() As System.Collections.Generic.List(Of Product) Implements IProductRepository.GetProducts\n        Dim Parameter As New Parameter()\n        Parameter.Type = CommandType.StoredProcedure\n        Parameter.Text = "spGetProducts"\n        Dim productList As List(Of Product)\n        productList = MyBase.ExecuteReader(Parameter)\n        Return productList\n    End Function\n\n    \'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\n    Public Overrides Function BuildEntity(ByVal cmd As System.Data.SqlClient.SqlCommand) As System.Collections.Generic.List(Of Product)\n        Dim productList As New List(Of Product)\n        Using reader As SqlDataReader = cmd.ExecuteReader()\n            Dim product As Product\n            While reader.Read()\n                product = New Product()\n                product.ID = reader("ProductID")\n                product.SupplierID = reader("SupplierID")\n                product.CategoryID = reader("CategoryID")\n                product.ProductName = reader("ProductName")\n                product.QuantityPerUnit = reader("QuantityPerUnit")\n                product.UnitPrice = reader("UnitPrice")\n                product.UnitsInStock = reader("UnitsInStock")\n                product.UnitsOnOrder = reader("UnitsOnOrder")\n                product.ReorderLevel = reader("ReorderLevel")\n                productList.Add(product)\n            End While\n            If productList.Count &gt; 0 Then\n                Return productList\n            End If\n        End Using\n        Return Nothing\n    End Function\nEnd Class\n</code></pre>\n\n<p><strong>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</strong></p>\n\n<p><strong>below is the simple testing base class I used</strong></p>\n\n<pre><code>Imports System.Configuration\nImports System.Data\nImports System.Data.SqlClient\nImports Microsoft.VisualStudio.TestTools.UnitTesting\n\nPublic MustInherit Class TransactionFixture\n    Protected mConnection As IDbConnection\n    Protected mTransaction As IDbTransaction\n    Private mConnectionString As String = ConfigurationManager.ConnectionStrings("Northwind.ConnectionString").ConnectionString\n\n    &lt;TestInitialize()&gt; _\n    Public Sub CreateConnectionAndBeginTran()\n        mConnection = New SqlConnection(mConnectionString)\n        mConnection.Open()\n        mTransaction = mConnection.BeginTransaction()\n    End Sub\n\n    &lt;TestCleanup()&gt; _\n    Public Sub RollbackTranAndCloseConnection()\n        mTransaction.Rollback()\n        mTransaction.Dispose()\n        mConnection.Close()\n        mConnection.Dispose()\n    End Sub\nEnd Class\n</code></pre>\n\n<p><strong>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</strong></p>\n\n<p><strong>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</strong></p>\n\n<pre><code>Imports SampleApplication.Library\nImports System.Collections.Generic\nImports Microsoft.VisualStudio.TestTools.UnitTesting\n\n&lt;TestClass()&gt; _\nPublic Class ProductRepositoryUnitTest\n    Inherits TransactionFixture\n\n    Private mRepository As ProductRepository\n\n    &lt;TestMethod()&gt; _\n    Public Sub Should-Insert-Update-And-Delete-Product()\n        mRepository = New ProductRepository(New HttpCache(), mConnection, mTransaction)\n        \'** Create a test product to manipulate throughout **\'\n        Dim Product As New Product()\n        Product.ProductName = "TestProduct"\n        Product.SupplierID = 1\n        Product.CategoryID = 2\n        Product.QuantityPerUnit = "10 boxes of stuff"\n        Product.UnitPrice = 14.95\n        Product.UnitsInStock = 22\n        Product.UnitsOnOrder = 19\n        Product.ReorderLevel = 12\n        \'** Insert the new product object into SQL using your insert sproc **\'\n        mRepository.InsertProduct(Product)\n        \'** Select the product object that was just inserted and verify it does exist **\'\n        \'** Using your GetProductById sproc **\'\n        Dim Product2 As Product = mRepository.GetProduct(Product.ID)\n        Assert.AreEqual("TestProduct", Product2.ProductName)\n        Assert.AreEqual(1, Product2.SupplierID)\n        Assert.AreEqual(2, Product2.CategoryID)\n        Assert.AreEqual("10 boxes of stuff", Product2.QuantityPerUnit)\n        Assert.AreEqual(14.95, Product2.UnitPrice)\n        Assert.AreEqual(22, Product2.UnitsInStock)\n        Assert.AreEqual(19, Product2.UnitsOnOrder)\n        Assert.AreEqual(12, Product2.ReorderLevel)\n        \'** Update the product object **\'\n        Product2.ProductName = "UpdatedTestProduct"\n        Product2.SupplierID = 2\n        Product2.CategoryID = 1\n        Product2.QuantityPerUnit = "a box of stuff"\n        Product2.UnitPrice = 16.95\n        Product2.UnitsInStock = 10\n        Product2.UnitsOnOrder = 20\n        Product2.ReorderLevel = 8\n        mRepository.UpdateProduct(Product2) \'**using your update sproc\n        \'** Select the product object that was just updated to verify it completed **\'\n        Dim Product3 As Product = mRepository.GetProduct(Product2.ID)\n        Assert.AreEqual("UpdatedTestProduct", Product2.ProductName)\n        Assert.AreEqual(2, Product2.SupplierID)\n        Assert.AreEqual(1, Product2.CategoryID)\n        Assert.AreEqual("a box of stuff", Product2.QuantityPerUnit)\n        Assert.AreEqual(16.95, Product2.UnitPrice)\n        Assert.AreEqual(10, Product2.UnitsInStock)\n        Assert.AreEqual(20, Product2.UnitsOnOrder)\n        Assert.AreEqual(8, Product2.ReorderLevel)\n        \'** Delete the product and verify it does not exist **\'\n        mRepository.DeleteProduct(Product3.ID)\n        \'** The above will use your delete product by id sproc **\'\n        Dim Product4 As Product = mRepository.GetProduct(Product3.ID)\n        Assert.AreEqual(Nothing, Product4)\n    End Sub\n\nEnd Class\n</code></pre>\n\n<p>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 ;)</p>\n'}, {'answer_id': 161060, 'author': 'thvo', 'author_id': 13041, 'author_profile': 'https://Stackoverflow.com/users/13041', 'pm_score': 2, 'selected': False, 'text': "<p>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). </p>\n\n<p>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.</p>\n\n<p>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.</p>\n"}, {'answer_id': 161851, 'author': 'AJ.', 'author_id': 7211, 'author_profile': 'https://Stackoverflow.com/users/7211', 'pm_score': 2, 'selected': False, 'text': '<p>Good question. </p>\n\n<p>I have similar problems, and I have taken the path of least resistance (for me, anyway). </p>\n\n<p>There are a bunch of other solutions, which others have mentionned.  Many of them are better / more pure / more appropriate for others. </p>\n\n<p>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.  </p>\n\n<p>I know, I know.  This sounds terrible, but what I need is to get off the ground with <em>some</em> 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.  </p>\n'}, {'answer_id': 800759, 'author': 'MatthewMartin', 'author_id': 33264, 'author_profile': 'https://Stackoverflow.com/users/33264', 'pm_score': 1, 'selected': False, 'text': "<p>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.</p>\n\n<pre><code>/*\n\n--setup\nDeclare @foo int Set @foo = (Select top 1 foo from mytable)\n\n--test\nexecute wish_I_had_more_Tests @foo\n\n--look at rowcounts/look for errors\nIf @@rowcount=1 Print 'Ok!' Else Print 'Nokay!'\n\n--Teardown\nDelete from mytable where foo = @foo\n*/\ncreate procedure wish_I_had_more_Tests\nas\nselect....\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/12374', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1078/']