Regression tests for T-SQL stored procedures

Question

I would like to regression test t-sql stored procedures. My idea is to specify for each SP multiple input parameter sets. The SP should be executed with these parameters, results should be written to disc. Next time the new results should be compared with results stored before.

Does anybody know a good tool for something like that? Should not be that hard to implement, but in practice you will need functionality like "ignore that column" or something like that. And I would assume that such a tool should already exist!?

cheers, Achim

Answer 1

I would use a unit test suite like DbUnit. DbUnit will set up your database, run the stored procedure and then verify the result, and optionally roll back the database ready for the next test. It represents input data and expected results as XML files, which you can reverse engineer from the data and schema already in the database.

To regression test, you use db unit to run the stored procedures under test once, and collect the output (without validating it). This defines your baseline that you run subsequent regression tests against.

DbUnit has a nice abstraction for tabular data, which can be fetched from CVS files, XML files or database objects.

The test inputs and expected results are are easy to diff and store in version control, since they are stored as XML. As mentioned, you can also load and save data from CSV files.

DbUnit is a great tool for testing database function, the project page, and the FAQ are good resources to get started.