8月22日
Unit Testing Stored Procedures in MS SQL Sever
I was looking for a good unit testing tool for my stored procedures, was looking some thing on lines of NUnit,Junit.
I found this tool TSQLunit at
http://tsqlunit.sourceforge.net/index.html Initially i thought of writing my own tool,which logs the test results and give the report of the test cases failed,passed and the reason for failure. TSQLUnit exactly does the same thing. Its a simple and good tool, though it cannot be compared with the likes of NUnit and Junit.
You can downlaod TSQLUnit from here
http://tsqlunit.sourceforge.net/tsqlunit_download.htm . It has a simple Zip file, contains two sql files:
1)Tsqlunit.sql --> This installs the storedprocs and the tables used by this tool.
2)RemoveTsqlunit.sql --> This un-installs the storedprocs and the tables used by this tool.
Example:
Lets say that we need to test, if the database has trigger present it.
First we write the unit test script(stored procedure)
CREATE PROCEDURE ut_testForTrigger AS
BEGIN
DECLARE @SPcount int
SELECT @SPCOunt=count(*) from sysobjects where xtype='tr'
if @SPCOunt<1
EXEC tsu_failure 'No triggers!!!!!!!!!'
END
Then execute the unit test cases :
EXEC dbo.tsu_runTests
This will return the message, if the test case is passed of failed(find the error message below)
================================================================================
Run tests starts:Aug 22 2005 4:43PM
================================================================================
Testsuite: (1 tests ) execution time: 16 ms.
>>> Test: ut_testForTrigger No triggers!!!!!!!!!
--------------------------------------------------------------------------------
Run tests ends:Aug 22 2005 4:43PM
Summary:
1 tests, of which 1 failed and 0 had an error.
FAILURE!
--------------------------------------------------------------------------------
================================================================================
I am planning to write some more add-ins to enhance the functionality of the tool
Exporting the Test results to flat file:- Some times we need to export the results of unit test cases into Text file (may be latter sending it as an attachment for all the team mates). Run the below command to export the results to text file
bcp "EXEC dbo.tsu_runTests" queryout <YourFileName>.txt -S<YourServername> -T -m0 -w
ツール 
