Sample Code from Today’s Webcast

I was asked if I could provide some of the code samples that I showed during today’s webcast. So I thought I’d post them right here.

 

One example I showed was how to use the regular expression generator to generate phone numbers. The expression I used was the following.

 

[0-9]{3}-[0-9]{3}-[0-9]{4}

 

You could make this expression fancier to only generate numbers in a specific area code, etc.

 

Next I created a unit test for a GetAllAuctions stored procedure that I had created. I used the script generation facilities to automatically generate the stub script. After doing that, I added a SQL assertion to verify that the correct number of rows were returned from the query. The SQL to check this is as follows:

 

IF (@@ROWCOUNT <> 50)

RAISERROR('GetAllAuctionsTest Error: Unexpected row count',1,1)

 

Of course you could accomplish the same verification using a Row Count test condition.

 

In addition, I wrote a unit test for a CreateAuction stored procedure. I modified the automatically generated script to look like this:

 

-- db unit test for dbo.CreateAuction

DECLARE @AuctionID Int,

      @AuctionTitle NVarChar( 50 ),

      @AuctionDescription NVarChar( 2000 ),

      @AuctionStartDate DateTime,

      @AuctionLength Int,

      @ProductID Int,

      @ItemQuantity Int,

      @EmployeeID Int,

      @AuctionTypeID Int,

      @MininumBidAmount Money

SELECT @AuctionID = 0,

      @AuctionTitle = 'Chai',

      @AuctionDescription = '2 25-pack boxes of India Chai',

      @AuctionStartDate = '6/14/2006',

      @AuctionLength = 10,

      @ProductID = 1,

      @ItemQuantity = 2,

      @EmployeeID = 1,

      @AuctionTypeID = 1,

      @MininumBidAmount = 10.00

EXEC @AuctionID = [dbo].[CreateAuction] @AuctionTitle , @AuctionDescription , @AuctionStartDate , @AuctionLength , @ProductID , @ItemQuantity , @EmployeeID , @AuctionTypeID , @MininumBidAmount

--query AuctionTitle, AuctionStartDate of created auction

--to verify appropriate results

SELECT AuctionTitle, AuctionStartDate

FROM Auctions WHERE AuctionID = @AuctionID

--delete created auction to return db to previous state

DELETE FROM Auctions WHERE AuctionID = @AuctionID

This script assigns values to the parameters of the sproc and then executes the sproc. I them select the auction title and auction start date of the auction that was just created. I would then add 2 scalar value test conditions to verify that the actual values returned is what I expected. The last statement deletes the newly created auction to return the database to its previous state.

 

So those are a few quick samples from the webcast. I’ll be trying to get some more involved unit test examples up here in the near future.

 

Sachin Rekhi

Comments

• Anonymous
  August 10, 2006
  That was very good to post the deom code for today but I would also like to access the demo code for the other sessions in this series.  Also, am I mistaken that the part 2 webcast will not be done until August 30th? That is the information I was given when I registerd for the "MSDN Webcast: Visual Studio 2005 Team Edition for Database Professionals (Part 2 of 4): Managing and Deploying SQL Server 2005 Schemas".  Start Time: Wednesday, August 30, 2006 9:00 AM Pacific Time (US & Canada)
  
  Thank you,
  David Baker