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.