What is the best way to test a stored procedure?

Question

Like many companies that require all access be through stored procedures, we seem to have a lot of business logic locked away in sprocs. These things are just plain hard to test, and some of them have become silly long. Does anyone out there have a set of best practices that can make it a little easier to confidently test these things?

At present we maintain 30 or so "Problem" databases that we run against. This isn't always particularly well documented and it sure isn't automated.

Answer 1

A colleague swears by the TSQLUnit testing framework. May be worth a look for your needs.

Answer 2

Not sure if this is what you're looking for, but since you're using SQL Server: I've found LINQ to be a great tool test stored procs. You can just drag the stored procedures onto a DBML diagram and then call them as methods on your datacontext. Beats setting up ADO connections etc for a test harness. If you set up a test project in Visual Studio for example, you can simply test your procedures like methods on another object. If your stored procs return result sets, I think LINQ will translate that into anonymous variables that you should be able to access via IEnumerable or IQueryable (somebody pls verify this). But if you're returning return codes only, this should be a quick and fairly easy way.

Answer 3

I noticed your post was tagged as SqlServer. If that's the case, then you should look at the Team Edition for Database Professionals that is part of Visual Studio. Here's some articles:

• A tutorial I wrote on TDDing Stored Procs with DBPro
• an MSDN magazine article which goes more in-depth
• DbFit, a framework by Gojko Adzic which integrates to FIT and Fitnesse to do functional testing of databases

The last one is actually cross-DB platform, while DBPro is solely SQL Server for now.

Answer 4

That seems like a terrible policy. Perhaps you can write a stored procedure that executes SQL and begin to transition your code to run through there.

In any case, I would test calling the stored procedures via a traditional automation framework. As the gateway between the application and the data, these should be handled as integration tests, rather than pure unit tests. However, you can use an xUnit based unit testing framework to drive them. As long as your tests have access to run SQL against the database, perhaps through the method I mentioned previously, you should be able to assert that the correct changes were made.

One challenge is that you indicate they are getting lengthy. I would recommend breaking them into subroutines and making them as small as possible. It makes it easier to test, and easier to maintain.

Answer 5

We had a very thin Data Access layer which basically facaded stored procedures to look like C# methods. Our NUnit test-suite then had SetUp/TearDown to create/rollback a transaction and test methods that called into DAL. Nothing fancy, and proved to be easier to maintain than TSQLUnit test-suite.

Answer 6

Here's my low-tech, quickie method of just keeping example inputs conveniently located in the DDL

USE [SpacelySprockets]

GO

/****** Object:  StoredProcedure [dbo].[uspBrownNoseMrSpacely]    Script Date: 02/03/3000 00:24:41 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--================================

--Stored Procedure DDL:

--================================

--Example Inputs

/*

DECLARE @SuckupPloyId     int

DECLARE @SuckupIdentityRecordId     int 

SET     @SuckupPloyId    = 3

*/

-- =============================================

-- Author:  6eorge Jetson

-- Create date: 01/02/3000

-- Description:    Sucks up to the boss

-- =============================================

CREATE PROCEDURE [dbo].[uspBrownNoseMrSpacely]

    @SuckupPloyId    int

   ,@SuckupIdentityRecordId int OUTPUT 

AS

BEGIN
    DECLARE @EmployeeId  int
    DECLARE @SuckupPoints int
    DECLARE @DateTimeStamp datetime

    SET  @EmployeeId  = dbo.svfGetEmployeeId('6eorge Jetson')
    SET  @SuckupPoints = dbo.svfGetSuckupPoints(@SuckupPloyId)
    SET  @DateTimeStamp = getdate()

    --Data state-changing statement in sproc
    INSERT INTO [dbo].[tblSuckupPointsEarned]([EmployeeId], [SuckupPoints], [DateTimeStamp] )
    VALUES (@EmployeeId, @SuckupPoints, @DateTimeStamp)

    SET @SuckupIdentityRecordId = @@Identity
END

--Unit Test Evidence Display
/*
SELECT
     @EmployeeId as EmployeeId
    ,@SuckupPoints as SuckupPoints
    ,@DateTimeStamp as DateTimeStamp
*/


--==========================================================================
--After editing for low-tech, non-state changing "unit-like" test invocation
--==========================================================================

--Example Inputs
DECLARE @SuckupPloyId      int
DECLARE @SuckupIdentityRecordId  int 
SET  @SuckupPloyId    = 3

/*
-- =============================================
-- Author:   6eorge Jetson
-- Create date: 01/02/3000
-- Description: Sucks up to the boss
-- =============================================
CREATE PROCEDURE [dbo].[uspBrownNoseMrSpacely]
     @SuckupPloyId    int
    ,@SuckupIdentityRecordId int OUTPUT 
AS
BEGIN
*/
    DECLARE @EmployeeId  int
    DECLARE @SuckupPoints int
    DECLARE @DateTimeStamp datetime

    SET  @EmployeeId  = dbo.svfGetEmployeeId('6eorge Jetson')
    SET  @SuckupPoints = dbo.svfGetSuckupPoints(@SuckupPloyId)
    SET  @DateTimeStamp = getdate()

    --Data state-changing statement now commented out to prevent data state change
--  INSERT INTO [dbo].[tblSuckupPointsEarned]([EmployeeId], [SuckupPoints], [DateTimeStamp] )
--    VALUES (@EmployeeId, @SuckupPoints, @DateTimeStamp)

    SET @SuckupIdentityRecordId = @@Identity

--END   --Need to comment out the sproc "END" also

--Unit Test Evidence Display
SELECT
     @EmployeeId as EmployeeId
    ,@SuckupPoints as SuckupPoints
    ,@DateTimeStamp as DateTimeStamp

It works even better for udfs as there is no change of state to worry about. Clearly, I wouldn't recommend this in lieu of a testing framework, but if I stick to this simple seconds-costing discipline of

Assert that my managable-sized sproc passes at least a simple "unit test"

prior to executing CREATE PROCEDURE, I find that I make fewer mistakes (likely due to discipline more than the test itself).

Answer 7

One method that I've used is to write a 'temporary' unit test for refactoring a particular stored procedure. You save the data from a set of queries from a database, and store them somewhere where a unit test can get at them.

Then, refactor your proc stock. The data returned should be the same, and can be compared directly against the saved data, automatically or manually.

An alternative is to run the two stored procedures in parallel, and compare the result sets.

This works particularly well for select-only stored procedures, but updates, inserts & deletes are more complex.

I've used this method to get the code to a state where it is more susceptible to unit testing, or simpler, or both.