Take the 2-minute tour ×
Software Quality Assurance & Testing Stack Exchange is a question and answer site for software quality control experts, automation engineers, and software testers. It's 100% free, no registration required.

I'm looking to create a batch of automated tests in SQL on MS-SQL server, these will include tests like - insert some records, call stored procedure, check certain conditions in various tables. There will be other tests which will just be complicated queries - ie. many look like unit tests but will include deep knowledge of the product.

It would be helpful if these tests could be run on a customer site as well as in the office with minimal interference and maximum portability.

I was thinking of just going down the route of a stored procedure for each test and writing out detailed text as the results for each FAIL, and just a "Test number 99 - PASS" otherwise.

Would a more formal / commercial test harness be helpful? Thoughts?

share|improve this question
1  
Assuming you're using SQL server 2k5+, have you looked into tSQLt? –  a1210 Jul 21 '12 at 20:14
add comment

3 Answers

Use an established free open source framework like tSQLt? See tSQLt.org for more details. Tests are implemented as stored procedures and the framework is included as part of the database.

share|improve this answer
add comment

Another approach that might help with the portability is to run you SQL from an application test harness. If you use the SQL inline with this you have the ability to take the exe to any installation and just run it. To expand on using a testing framework there are several really good articles on Simple Talk about Unit testing sql code. They are good reads.

Sept 07 - http://www.simple-talk.com/dotnet/.net-tools/testing-times-ahead-extending-nunit/
March 08 http://www.simple-talk.com/dotnet/.net-tools/testing-times-ahead-extending-nunit/
Jan 2010 http://www.simple-talk.com/sql/database-administration/close-those-loopholes-lessons-learned-from-unit-testing-t-sql/

share|improve this answer
add comment

Visual Studio 2010 has some built in tools for unit testing SQL server. This article is a good read: http://blogs.msdn.com/b/atverma/archive/2010/07/28/how-to-unit-test-sql-server-2008-database-using-visual-studio-2010.aspx

I know that Red-Gate also has some good tools for testing SQL server, especially around comparing tables or results of queries from two different versions.

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.