Introducing TSQLUnit
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, 測試設定
2) execution of the target procedure, and 執行測試目標預存程序.
3) checking results.檢查結果.

In the unit test setup, I often check to make sure someone hasn't done bad things to my data when I wasn't looking:

DECLARE @nId INT, @nNewId INT —- @nNewId is for later
SELECT @nId = [ID] FROM MyTable WHERE MyField = 'whatever'
IF @nId IS NULL  -- or @@ROWCOUNT = 0
  EXEC tsu_failure 'The data has changed. ''whatever'' couldn''t be found'

The IF block checks for the expected record. If it couldn't be found, the test fails and will generate an error message. The test framework moves on to the next unit test. You don't need to use the name of the unit test in the failure message string, because TSQLUnit will name it for you when the test fails.
Now I call the stored procedure I'm about to write:
EXEC CreateMyTableNewRec @nId, @nNewId OUTPUT

As you can see, I've determined that I need an output parameter from this new procedure. In checking the results, I make sure the output parameter really is filled with something:
  EXEC tsu_failure  'A new record was not created for table MyTable.'

I could further check the value to see if the new record was created in the way I wanted it to be created.
Each TSQLUnit test is itself a stored procedure. Listing 1 shows what one looks like when all of the pieces are put together:
Listing 1. A complete unit test for T-SQL.

  --== Setup ==--
  SELECT @nId = ID FROM MyTable
  WHERE MyField = 'whatever'
  IF @nId IS NULL  -- or @@ROWCOUNT = 0
    EXEC tsu_failure 'The data has changed.
    ''Whatever'' couldn''t be found'
  --== Execute ==--
  EXEC CreateMyTableNewRec @nId, @nNewId OUTPUT
  --== Check ==--
  IF @nNewId IS NULL
    EXEC tsu_failure 'A new record was not created
    for table MyTable.'

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"–uniquely identifies this unit test.
Note that you don't need BEGIN TRAN and ROLLBACK in each unit test; TSQLUnit takes care of this for you.
你也不在需要BEGIN TRAN和ROLLBACK在每個單元測試.TSQLUnit負責為你處理

Running the unit test
In order to run the 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 haven't already. Now you're set. Simply execute the unit test:

-- This will run all tests for suite MyTable, 這將運行MyTable組的所有測試
EXEC tsu_RunTests MyTable

Fixtures 裝置
Suppose I want numerous records to be available for all the unit tests of a suite. I don't 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 run before each unit test.

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:
  INSERT INTO MyTable ([Description])
  VALUES ('something')
  --( more records inserted here

The SQL Server community owes a huge debt of gratitude to Henrik Ekelund and his employer for making TSQLUnit open source.
SQLServer社區極大的感激Henrik Ekelund和他的職員讓TSQLUNnit開放源碼.

Link to http://sourceforge.net/projects/tsqlunit

Link to http://tsqlunit.sourceforge.net/tsqlunit_cookbook.htm (documentation)


Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >



如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。