Can this method for unit testing a stored procedure be improved?

Question

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.

Answer 1

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

Answer 2

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

Luck.

Answer 3

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.