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

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.

• Is there a better way (or standard way) to test a stored procedure

like this?

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

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

StackExchange Database Administrators Q#5288, answer score: 4