Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.
share|improve this question
add comment

3 Answers

up vote 4 down vote accepted

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

share|improve this answer
1  
Do you mind summarizing the essence of your solution here? Link-only answers are generally better posted as comments. –  Nick Chammas Jan 10 '12 at 17:35
add comment

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.

share|improve this answer
add comment

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

Luck.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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