Geek Answers Handbook

Saved SQL Testing Procedures

I am new to working with an SQL database, but have come across testing my database. For security reasons, any requests or updates are performed using stored procedures.

I was suggested to use stored procedures to check other stored procedures. Is this a good or bad solution for unit testing my stored procedures to make sure they do what they intend to do?

+7
source share
5 answers

I found a great solution using Visual Studio: Testing Visual Studio Modules

It allows you to create unit tests for SQL stored procedures, you can also populate the database with regular expressions - very cool stuff.

+8
source

Besides DbUnit and NUnit, you can use tSQLt if you like to write tsql. tSQLt is an open source framework written in tsql to write unit test cases for tsql.

+5
source

I do not know which tool can perform unit test for SQL statements or storage procedures. I usually write a SQL script to do a test for this.

  • Create an empty database, of course, the same structure as the original one.
  • Prepare the data (import some data from the source database).
  • Call the stored procedure.
  • Then look at the data if it is true or not.

Sometimes they write some statements, such as

IF EXISTS (SELECT * FROM XX INNER JOIN XXX ON XXXXXXXX WHERE XXX=XXX) RAISEERROR XXXXXX

If you encounter some exceptions or errors, you can check the stored procedure.

But this is a waste of time.

Usually I check the entire execution path of the stored procedure most of the time and just check the main pending error points.

+1
source

I use TST as the testing base for SQL Server with very positive results. It works well, and I could integrate it as part of our CI environment in Team City.

The key must be very careful about how to set up test data. Regardless of which test structure you choose, be sure to apply the Data Data Builder template. If you do not, you can get some problems refactoring the test very quickly.

+1
source

DbUnit from dbunit.org is a good structure. Other useful tools are TSqlUnit and tsqlt from tsqlt.org.

+1
source

More articles:


All Articles