Introducing TSQLUnit TSQLUnit Profile TSQLUnit is an open source unit testing framework for T-SQL written by Henrik Ekelund and available from http://sourceforge.net/projects/tsqlunit. Here's an example of how I've used it.
My TSQLUNIT TESTS TAKE A SIMILAR PATTERN OF Three Parts: 1) Unit Test Setup, Test Settings 2) Execution Of The Target Procedure, and Decrease Test Target Store .3) Checking Results. Check results.
In The Unit Test Setup, I Off To Make Sure Somene Hasn't Done Bad Things To My Data When I Wasn't Looking: In the unit test, I often check that other people don't destroy the data I hope:
Declare @nid int, @nnewid Int - @nnewid is for latelect @nid = [id] from myTable where myfield = 'wherever'if @nid is null - or @@ rowcount = 0 Exec TSU_Failure' The Data Has Changed. '' Whatver '' COULDN' '' 'BE FOUND'
The IF block checks for the expected record. If it could not be found, the test fails and will generate an error message. The test framework moves on to the next unit test. You do not need to use the name of the unit Test in The Failure Message String, Because Tsqlunit Will Name It for You1en Test Fails.if Block Checks the desired record. If you can't find it, the test fails and generate an error message. Test frame moves to the next unit test. You don't have to be The name of the test is used in the failed message, because Tsqlunit is named when the test fails .Now i call the store: Now I call the stored procedure I want to write: Exec createmyTablenewrec @nid, @nnewid Output
As you can see, I'VE DETERMINED That I Need An Output Parameter from this New Procedure. I make Sure Something: You see, I will check the parameters you need to return during the stored procedure. In the results of the inspection, it is true that the output parameter is really filled. IF @nnewid is null exec tsu_failure 'a new record Was Not create for table myTable.'
I Could Further Check The Value To See eti the new record was created in the way I wanted it to be create, if I ask new records being created. Each TsqlUnit Test Is Itself A Stored Procedure Listing 1 Shows What One Looks Like When All of the PieECES Are Put Toget: Each TSQLUnit test is a stored procedure. Listing 1 shows the situation of all test paragraphs. LISTING 1. A Complete Unit Test for t-sql .CREATE PROCEDURE ut_MyTable_NewRecAS - == Setup == - DECLARE @nID INT, @nNewId INT SELECT @nId = ID FROM MyTable WHERE MyField = 'whatever' IF @nId IS NULL - or @@ ROWCOUNT = 0 EXEC tsu_failure ' The Data Has Changed. '''Ver '' COULDN' '' = EXECUTE == - EXEC CREATEMYTABLENEWREC @nid, @nnewid output - @nnewid == - if @nnewid is null exec TSU_Failure 'A New Record Was Not Created for Table MyTable.'go
Note the three-part name of the stored procedure, ut_MyTable_NewRec. The prefix "ut_" alerts TSQLUnit that this is a unit test it should run. If you already use this prefix ut_ for other purposes, TSQLUnit lets you set it to something else. "MyTable" is the name of a group of related unit tests, known as a suite of tests. For instance, you could add another unit test called ut_MyTable_DeleteRec. The MyTable suite would test both adding and deleting a record to MyTable. The suite can BE Run Separately from Other Test Suites. The Third Part of The Name- "Newrec" or "deleterec" -unique Identifies this unit test. The prefix UT_ is a unit test that tells Tsqlunit to run. if You have used UT_ prefix as other purposes, TSQLUnit requires you to modify other names. The second paragraph of the name shows the group of tests, for example, you can join a unit test name is UT_MYTABLE_DELETEREC. This group will test Add And delete a record to myTable, the group can also be separated from other test groups. The third paragraph of the name is the unique indication of the test.Note That You don't need Begin TRAN AND ROLLBACK IN EACH UNIT TEST; TSQLUnit Takes Care Of this for you. You are not needing Begin Tran and Rollback at each unit test .tsqlunit is responsible for the Running The Unit Test Running Unit Test in Order to Run TH e unit test in Listing 1, you need to set up the framework. From Query Analyzer, run tsqlunit.sql on your development database. You need do this only once for the database. Next, create procedure ut_MyTable_NewRec, if you have not already Now you're set. Simply Execute The Unit Test: In order to run the unit test in the list 1, you have to set the test framework. In the Query Analyzer, run Tsqlunit.sql in your development database .. You just want to execute Once in the database. Create UT_MYTABLE_NEWREC, now you can make a simple execution unit test:
- This Will Run All Tests for Suite MyTable, this will run all tests of the MyTable group EXEC TSU_RUNTESTSMYTABLE
Fixtures Equipment Suppose I want numerous records to be available for all the unit tests of a suite. I do not want to write the same setup code for each test. TSQLUnit solves the problem with a setup fixture. The code in the fixture will be In a unit test group, I asked a lot of records to be valid, I don't want to write the same setup code every time, TSQLUnit uses a setup identity device: for instance, the setup fixture for the Previous MyTable Suite . would be named ut_MyTable_setup the third part of the name "setup" alerts TSQLUnit to treat the procedure as a setup fixture for the suite It will look something like this:. for example, prefix MyTable group ut_MyTable_Setup device may be used: named Setup will make TSQLUnit think this is a start-up device. The code is as follows: create procedure ut_mytable_setupas insert Into myTable ([Description) VALUES ('Something') - (More Records Inserted Herego
The SQL Server Community Owes a huge debt of gratitude to Henrik Ekelund and his Employer for Making Tsqlunit Open Source.sqlserver community great gratitude Henrik Ekelund and his staff let TSQLunnit open source.
Link to http://sourceforge.net/projects/tsqlunit
Link to http://tsqlunit.sourceforge.net/tsqlunit_cookbook.htm (documentation)
