One of the powerful features of Team Edition for Database Professionals is its ability to automatically generate a stub SQL test script for stored procedures, functions, and triggers.

 

This feature allows you to right-click on an existing sproc, function, or trigger in your database project in Solution Explorer, select Create Unit Tests…, and automatically generate a stub test script. This saves you considerable time in generating the boiler-plate code you will often need for your test.

 

Of course you will then need to customize the test script. At a bare minimum you need to specify the parameters to the sproc, function, or insert\update\delete statement (for triggers). You will then need to add whatever type of test verification you wish to.

 

I thought I would give you a quick tour of the types of test scripts you can automatically generate in the toolset.

 

Stored Procedures

The following is the generated test script for the dbo.CustOrderHist stored procedure in the Northwind database:

 

-- db unit test for dbo.CustOrderHist

DECLARE @RC INT,

      @CustomerID NCHAR (5)

 

SELECT @RC = 0,

      @CustomerID = NULL

 

EXEC @RC = [dbo].[CustOrderHist] @CustomerID

 

SELECT RC=@RC

 

As you can see, the first statement contained in the test script is a declaration of a variable for the return code as well as a variable for each parameter. The next statement then assigns default values to each variable. The execute statement invokes the sproc and finally we select the return code. This allows you to do verification on the return code since it is returned by the test script as its own resultset.

 

Functions

For a scalar-valued function dbo.GetZipCode, the generated test script would look like the following:

 

-- db unit test for dbo.GetZipCode

DECLARE @RC VARCHAR (5),

      @City VARCHAR (100),

      @StateCode VARCHAR (2)

 

SELECT @RC = NULL,

      @City = NULL,

      @StateCode = NULL

 

SELECT @RC = [dbo].[GetZipCode]( @City, @StateCode)

SELECT RC=@RC

 

Similar to sprocs, we are declaring and assigning variables, executing the function, and selecting over the results.

 

A test script for the table-valued function dbo.GetAllZipCodes would look like the following:

 

-- db unit test for dbo.GetAllZipCodes

DECLARE @state CHAR (2)

 

SELECT @state = NULL

 

SELECT * FROM [dbo].[GetAllZipCodes]( @state)

 

Triggers

The generated script for a trigger will depend on the type of events the trigger acts on (insert, update, or delete). The following script is for a trigger defined on the customers table. It acts on all of the above events.

 

-- db unit test for dbo.TriggerCustomers

DECLARE @CustomerID NCHAR (5),

      @CompanyName NVARCHAR (40),

      @ContactName NVARCHAR (30),

      @ContactTitle NVARCHAR (30),

      @Address NVARCHAR (60),

      @City NVARCHAR (15),

      @Region NVARCHAR (15),

      @PostalCode NVARCHAR (10),

      @Country NVARCHAR (15),

      @Phone NVARCHAR (24),

      @Fax NVARCHAR (24)

 

SELECT @CustomerID = NULL,

      @CompanyName = NULL,

      @ContactName = NULL,

      @ContactTitle = NULL,

      @Address = NULL,

      @City = NULL,

      @Region = NULL,

      @PostalCode = NULL,

      @Country = NULL,

      @Phone = NULL,

      @Fax = NULL

 

INSERT INTO [dbo].[Customers] ( CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax )

      VALUES( @CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax )

 

--DECLARE @CustomerID NCHAR (5),

--    @CompanyName NVARCHAR (40),

--    @ContactName NVARCHAR (30),

--    @ContactTitle NVARCHAR (30),

--    @Address NVARCHAR (60),

--    @City NVARCHAR (15),

--    @Region NVARCHAR (15),

--    @PostalCode NVARCHAR (10),

--    @Country NVARCHAR (15),

--    @Phone NVARCHAR (24),

--    @Fax NVARCHAR (24)

--

--SELECT @CustomerID = NULL,

--    @CompanyName = NULL,

--    @ContactName = NULL,

--    @ContactTitle = NULL,

--    @Address = NULL,

--    @City = NULL,

--    @Region = NULL,

--    @PostalCode = NULL,

--    @Country = NULL,

--    @Phone = NULL,

--    @Fax = NULL

--

--UPDATE [dbo].[Customers]

--    SET

--          [CustomerID] = @CustomerID,

--          [CompanyName] = @CompanyName,

--          [ContactName] = @ContactName,

--          [ContactTitle] = @ContactTitle,

--          [Address] = @Address,

--          [City] = @City,

--          [Region] = @Region,

--          [PostalCode] = @PostalCode,

--          [Country] = @Country,

--          [Phone] = @Phone,

--          [Fax] = @Fax

--    WHERE

--          [CustomerID] = @CustomerID AND

--          [CompanyName] = @CompanyName AND

--          [ContactName] = @ContactName AND

--          [ContactTitle] = @ContactTitle AND

--          [Address] = @Address AND

--          [City] = @City AND

--          [Region] = @Region AND

--          [PostalCode] = @PostalCode AND

--          [Country] = @Country AND

--          [Phone] = @Phone AND

--          [Fax] = @Fax

 

--DECLARE @CustomerID NCHAR (5),

--    @CompanyName NVARCHAR (40),

--    @ContactName NVARCHAR (30),

--    @ContactTitle NVARCHAR (30),

--    @Address NVARCHAR (60),

--    @City NVARCHAR (15),

--    @Region NVARCHAR (15),

--    @PostalCode NVARCHAR (10),

--    @Country NVARCHAR (15),

--    @Phone NVARCHAR (24),

--    @Fax NVARCHAR (24)

--

--SELECT @CustomerID = NULL,

--    @CompanyName = NULL,

--    @ContactName = NULL,

--    @ContactTitle = NULL,

--    @Address = NULL,

--    @City = NULL,

--    @Region = NULL,

--    @PostalCode = NULL,

--    @Country = NULL,

--    @Phone = NULL,

--    @Fax = NULL

--

--DELETE FROM [dbo].[Customers]

--    WHERE

--          [CustomerID] = @CustomerID AND

--          [CompanyName] = @CompanyName AND

--          [ContactName] = @ContactName AND

--          [ContactTitle] = @ContactTitle AND

--          [Address] = @Address AND

--          [City] = @City AND

--          [Region] = @Region AND

--          [PostalCode] = @PostalCode AND

--          [Country] = @Country AND

--          [Phone] = @Phone AND

--          [Fax] = @Fax

 

By default we have generated essentially 3 test scripts, one for each trigger event (insert, update, and delete). The second 2 are automatically commented out. At this point you should decide which event you wish to test for and delete the 2 other SQL statement blocks that are not relevant.

 

As you can see, for each trigger event type, we generate a SQL statement to perform the appropriate action. You simply need to assign the values for each of the table\view columns you wish to use as parameters to the insert\update\delete statements to complete the test script.

 

 

Are there other object types you would like to see us automatically generate test scripts for? Let us know what you had in mind!

 

Sachin Rekhi