When I have written unit tests for my stored procedures, they usually look something like this:

/* Unit test for stored procedure which runs a query */

/* Define #temp table with structure of query results */

/* Define and set variables for stored procedure parameters */ 

/* If stored procedure queries from a particular table...*/ 

/* Truncate table and populate with sample data */ 

/* Call stored procedure using variables but insert results into table */ 

/* INSERT #temp EXEC usp_TestProc @P1=1, @P2 = 'X' */

/* Query #temp table to confirm that all fields have certain values */

/* Query #temp table to confirm that certain number of rows exist */ 

/* If results are incorrect, display error message */ 

While this will work, it feels clunky somehow and is very difficult to maintain over the course of a long-term project where the requirements are constantly changing.

  1. Is there a better way (or standard way) to test a stored procedure like this?
  2. Is the difficulty of maintenance simply part of the job and worth the effort in order to have the unit tests? I have to say that at most of my jobs, the DBAs rarely do unit testing in the first place.

3 Answers 3


I have written a series of blogs on unit testing select stored procedures using DBTestUnit that might be of use.

  • 1
    Do you mind summarizing the essence of your solution here? Link-only answers are generally better posted as comments. Jan 10, 2012 at 17:35

Use, for example, TST - TSQL Test Tool. It will automate your process. See other solutions I asked on sqa



I have been doing unit testing T-SQL for more than four years so far. I think it much easier to use C#, which is more versatile. For example, in C# I have no problem unit testing a stored procedure that returns two or more result sets - it is plain impossible if you use T-SQL. I described the process in this article and in this blog post.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.