question

Ben Adderson avatar image
Ben Adderson asked

Unit testing stored procedures

Do you do it?

I'm talking specifically about the traditional definition of unit testing, as opposed to integration testing (where you would call your stored procedures through the same data access layer used by your application).

I'm aware there are SQL-based unit testing frameworks, does anyone have any experience using these, or perhaps writing C#/VB that directly tests the database (rather than via the DAL)?

t-sqlstored-proceduresvbunit-testing
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jjerome avatar image
jjerome answered

Yes, we use and find it extremely useful.

In our case, we are a (very) small team that supports a database that is vital to company business. Our code base is pretty complicated for what it is (C++ on top of SQL) and we don't really have the resources to make any large efforts to fix things. Instead, I've been coming up with ways of improving things with what we have.

I tried the Visual Studio Team test edition (or whatever it's called), but I found it to be a bit too much of an effort for our resources. Instead, we chose TSQLUnit and modified it a bit to serve our purposes. I essentially wrapped its functionality so that we could run the tests as a nightly job and send a summary email if any failures were found.

Next we just created unit tests (stored procedures) when we identified something in the system that we wanted to catch. Most times it's a small issue that ends up rolling into a larger issue if left unchecked. A lot of times it's something that we can't fix with our client software, so it's been a big help.

4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I'm particularly keen on Red-Gate's new 'SQL Test' which popped out of nowhere last week as a preview. It is a plug-in for SQL Server Management Studio that allows you to view all of your tests and results in a dockable panel, and share unit tests with team members. It uses the open-source tSQLt framework Free preview here.... http://bit.ly/uSyCyq
2 Likes 2 ·
@jjerome - have you ever come across tSQLt? Here at Red Gate we've collaborated with the tSQLt developers to build a UI test runner into SSMS. Would you (or anyone else!) be interested in helping us via a conference call or a remote Ux session? If so, please contact me at David dot Atkinson at red-gate.com . To get an idea of what we've done, visit www.sql-test.com
0 Likes 0 ·
@David I'm sure Jerome is long gone, but I am impressed that you/your company actually consults the community-many of which use Red Gate software. Kudos
0 Likes 0 ·
@Scott - Thanks! We consult out of necessity - how else would we get the feedback to develop products people would want to use! We'd appreciate some feedback on the SQL Test preview build that Phil Factor has referenced in his comment. Thanks all!
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered

We don't do it as much as we did, but my group went through a period where we were following test driven development within TSQL. We were using the SQL specific test suite, the name escapes me at the moment, and then we switched over to using the testing available in Visual Studio Team System Database Edition. We're still using that test suite some, but we've found that specific unit tests for stored procs, apart from the code, hasn't provided us much benefit. We get so much more from the unit tests run from the code side because that's where problems occur due to changes in stored procs, more often than not.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim Edlund avatar image
Tim Edlund answered

Our group has been doing unit testing on backend objects for about 2 years now. We require at least one unit test on each simple database object and on stored procedures the objective is to excercise all lines of the code which may require 3-10 tests depending on complexity. A performance test (response time) is require on all objects.

We are using Visual studio Team System Database Edition.

We think unit testing has important benefits, however, those benefits are not what we expected them to be when we started this practice. Our unit tests rarely find defects in the logic as written. Finding logic errors was why we originally thought we should be doing this.

What we found out was that unit tests help us to produce better code for other reasons. The first is by improving design accuracy. This occurs because our standard is to develop the test logic in advance of writing the module. We confirm how we are going to test by quickly showing some syntax in our stand-up meetings with other team members. By communicating how you are going to test something before you start building it you are forced to clarify your objectives and vision of the handshake with other tiers of the application. Before we followed this practice we inevitably ran into surprises because our team did not have a shared vision of how a backend component was going to supply a web service or the UI. We sometimes had to recode or rejig other tiers to adapt the design. The code today has a cleaner design with fewer data manipulations on other tiers.

The second main benefit is on production maintenance. Because the VS team system allows tests to be easily automated, we run the unit tests all the time and this helps find regression errors which have arisen from changes. A simple change such as changing a column from mandatory to non-mandatory may cause a procedure to fail because it was written with another assumption. The existence of unit tests along with good (and random) data generation allows the team to quickly and inexpensively conduct a round of testing that would be far too expensive to repeat manually for each change. Without unit tests, finding problems would typically wait until the unit acceptance test phase. Today we find them early and the quality we release to UAT is better.

Creating unit tests is more work and slower-up-front but our view is that in the long-run time is saved. Like most disciplines the more you do it the quicker you get at it.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatal Exception Error avatar image
Fatal Exception Error answered

Yes. Invaluable for finding bugs before they hit QA.

A must for making sure changes done do not affect the next tier due to the complexity of some stored procs and legacy systems being converted.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

PeterMarriott avatar image
PeterMarriott answered
Yes, we Unit Test some of the more complex stored procedures. We use dbFit based on the Fitnesse platform. http://www.fitnesse.info/dbfit It has been invaluable when refactoring complex sps. One sp springs to mind that has about thirty different test cases. By setting up the tests before I started changing anything it gave me the confidence to do some serious refactoring. We are looking at using it to test whole ETL jobs now (SSIS and Talend).
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered
Yes, though sometimes indirectly. Generally, I rely on the unit tests for the procedures in my main code base that calls on the stored procedure. For stored procedures that are custom written for the application, I know that as long as the applications unit tests pass the stored procedure functions propertly for all practical purposes. I know some would call this integration testing, but for that type of stored procedure it fulfills the same purpose. When I have a stored procedure or UDF that is not amenable to testing that way (because it is a maintaince script, or will be used in numerous external programs) then I write unit tests just for the SQL code, but even then I tend to write the unit tests in a library like Python's Unittest rather than a SQL Specific test suite.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

xiaoyuandlg avatar image
xiaoyuandlg Suspended answered
You may take a read for some [Code UI control][1]. [1]: http://www.kettic.com/winforms_ui/ui_test.shtml
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.