Using Test::More to unit test T-SQL

jplindstrom has asked for the wisdom of the Perl Monks concerning the following question:

I'm thinking of starting to unit test stored procedures written in T-SQL.

Now, T-SQL being as sucky as it is language-wise I immedidately thought of, instead of writing my own test harness in T-SQL, using an existing framwork for this. Say Test::More.

The general idea is to run SQL statements using DBI, begin a transaction, set up test data, run some SPs, inspect the new state with some selects and compare the result with Test::More's utility routines. When I'm done, i just rollback the transaction.

Has this been done before? Any thoughts in general about this?

Comment on Using Test::More to unit test T-SQL

Replies are listed 'Best First'.
Re: Using Test::More to unit test T-SQL by chromatic (Archbishop) on Feb 02, 2003 at 21:27 UTC
Some of the literature on testing databases suggests a `setup()` and a `teardown()` method before each set of tests. That way, you'll have a known state at each batch. Make sure that the state is restored even if a test crashes. I'd probably do that instead of the transaction approach. It's clever, but if you need to test something that itself requires a transaction, it's tricky.	[reply] [d/l] [select]