CODEWITHSUNDEEP
sql-server
Dan Blair
Dan Blair

Reputation: 2381

What is the best way to test a stored procedure?

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.

Upvotes: 33

Views: 67368

Answers (8)

Ben Hoffstein
Ben Hoffstein

Reputation: 103325

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

Upvotes: 13

Cory Foy
Cory Foy

Reputation: 7212

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:

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

Upvotes: 3

Ladi
Ladi

Reputation: 21

Try TST. You can download and install it from: http://tst.codeplex.com/

Upvotes: 2

Matthew Farwell
Matthew Farwell

Reputation: 61695

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.

Upvotes: 2

6eorge Jetson
6eorge Jetson

Reputation: 2235

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).

Upvotes: 1

Constantin
Constantin

Reputation: 28154

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.

Upvotes: 6

MattMcKnight
MattMcKnight

Reputation: 8290

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.

Upvotes: 1

Codewerks
Codewerks

Reputation: 5974

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.

Upvotes: 3

Related Questions