T.S.T - T-SQL Test Tool

In continuation with previous post, we will look at TST - TSQL Test Tool. Tool can be dowloaded from link. Documentation is very good and clear. Let's see step-by-step example for this tool .

Step 1 - Change to below value in TST.BAT File
SET TST_SqlServer=.\SQLExpress

Step 2. Executed Set up (TST is the core database required for running Tests)

Step 3. Setup TSTQuickStart Database (Example Database with Testcases in it)

Step 4. From previous posts, lets reuse the procs and write tests for the same
Reusing Tables and Procedure from post. AppDB is the Sample DB created for Demo.

Step 5. Create Test Procedure

CREATE PROCEDURE SQLTest_SaleCity

AS

BEGIN

DECLARE @City VARCHAR(100), @Sum INT

SET @City = 'Chennai'

DECLARE @TEMPDIAG TABLE (Name VARCHAR(100) NOT NULL, SaleDate DATETIME NOT NULL, SaleValue INT NOT NULL, City VARCHAR(100) NOT NULL)

INSERT INTO @TEMPDIAG

EXEC SaleCity @City

SELECT @Sum=SUM(SaleValue) FROM @TEMPDIAG

EXEC TST.Assert.Equals 'Sale in Chennai is', 9500, @Sum

END

GO

Step 6. Run the Test Procedure

Step 7. Creating Second Test Procedure

CREATE PROCEDURE SQLTest_SaleValue

AS

BEGIN

DECLARE @FromDate DATETIME, @ToDate DATETIME, @Sum INT

SET @FromDate = GETDATE()-100

SET @ToDate = GETDATE()

DECLARE @TEMPDIAG TABLE (Name VARCHAR(100) NOT NULL, SaleDate DATETIME NOT NULL,

SaleValue INT NOT NULL, City VARCHAR(100) NOT NULL)

INSERT INTO @TEMPDIAG

EXEC SaleValue @FromDate,@ToDate

SELECT @Sum=SUM(SaleValue) FROM @TEMPDIAG

EXEC TST.Assert.Equals 'Sum of Sales', 58090, @Sum

END

GO

Step 8. Run the Test Procedure

Benefits

• Ready to use
• Assertions are very helpful. More than 10 plus asserts are provided
• Generating XML results
• Exhaustive documentation

For DB test automation below parameters pass for TST - TSQL unit

• Ability to run suite of test cases
• Ability to run individual test case
• Handling Assertions without any extra coding
• Data Driven Testing can be implemented, This approach involves QA writing TSQL Code
• Below is a ideal case for Test Automation Scenario
• Invoke Web Services - Example post
• Invoke TST and verify values present

Good to have Additions

• Rerunning Failed Cases
• Email reports, GUI Interface

TST - is a good tool for DB Test Automation.

Demo Video



More Reads -
A Method For Testing Database Design
Database unit testing patterns
Taking this to next step, You can have a console app which can execute and email results.
Run a sql script file save the result in a text file and email it
Run all of these scripts against the database
Testing With Databases
CodePlex - xUnit.net - unit testing tool for the .NET Framework
TSQLUnit testing framework

Happy Reading!!