Today I’ve been on technical meeting where TSQLUnit was discussed as approach of testing database.

From what I understood it looks like this is very powerful and great tool for testing stored procedures and other execution code inside of SQL Server. It allows easily create setup for each test with minimal database schema, needed exactly for some particular stored procedure. After test got executed it rolls back to state before running test. Cool!

From my point of view this is really good thing to test stored procedures in isolated environment, as any good UT does with testing code. There is also question about integration of those tests with changes in real database and one great mind have produced using schemabindings in SQL Server.

It should be also possible to integrate TSQLUnit with CI by using NAnt.

Why?

There is only one thing I kept in my mind and did not talk about it during meeting. It is question: why do we still write lot of stored procedures? Yea, I understand that sometimes they are really needed and that there are some reporting projects that might require fetching lot of data at once. But during the meeting it looked like guys are going to use it for many other projects. Hm… From my point of view, this kind of tests should be written in regular programming language, executing some code and fetching needed data as it will be used further, after roll-backing inserted data – therefore we have integration tests. All them should run separately. And as per me this should be fine unless you have logic in database – I agree, that in this case we have to come to some database testing tools. Why should we have logic it in database unless there are some special requirements?

I still feel a bit frustrated, because for me it is quite hard to answer for the question of having logic in code or having some heavy portion of it in database. I vote for first variant, but maybe I do not have enough experience to understand why it is great to have logic in database. Who knows?

What do you think?