Example. Fixture and two tests for the Northwind database

CREATE PROCEDURE ut_salesbycategory_setup AS
BEGIN
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Products_Categories]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [dbo].[Products] DROP CONSTRAINT FK_Products_Categories
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Order_Details_Products]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [dbo].[Order Details] DROP CONSTRAINT FK_Order_Details_Products
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Products_Categories]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [dbo].[Products] DROP CONSTRAINT FK_Products_Categories
    
    TRUNCATE TABLE Categories
    INSERT INTO Categories (CategoryID, CategoryName)
        VALUES (1,'A category')

    TRUNCATE TABLE Products
    INSERT INTO Products (ProductID, ProductName, CategoryID, Discontinued)
        VALUES (1,'Some product',1,0)

    TRUNCATE TABLE Orders
    INSERT INTO Orders ( OrderID, OrderDate ) 
        VALUES (1,CONVERT(datetime,'1998-01-01',102))
END

CREATE PROCEDURE ut_salesbycategory_noDiscount AS
BEGIN
    DECLARE @total MONEY
    DECLARE @ok BIT
    SET @ok=0

    DELETE FROM [Order Details]
    INSERT INTO [Order Details]
              (OrderID, ProductID, UnitPrice, Quantity, Discount)
        VALUES(1,1,100,10,0)

    CREATE TABLE #temp(ProductName VARCHAR(500),
            TotalPurchase MONEY)
    INSERT INTO #temp 
        EXECUTE SalesByCategory 'A category', '1998'
    SET @total=(SELECT SUM(TotalPurchase) FROM #temp)

    IF @total = 1000 SET @ok=1
    IF @ok=0 EXEC tsu_failure 'The sum is not unitprice * quantity'
END
     
CREATE PROCEDURE ut_salesbycategory_discount AS
BEGIN
    DECLARE @total MONEY
    DECLARE @ok BIT
    SET @ok=0

    DELETE FROM [Order Details]
    INSERT INTO [Order Details]
              (OrderID, ProductID, UnitPrice, Quantity, Discount)
        VALUES(1,1,100,10,0.3)

    CREATE TABLE #temp(ProductName VARCHAR(500),
            TotalPurchase MONEY)
    INSERT INTO #temp 
        EXECUTE SalesByCategory 'A category', '1998'
    SET @total=(SELECT SUM(TotalPurchase) FROM #temp)

    IF @total = 100*10*(1-0.3) SET @ok=1
    IF @ok=0 EXEC tsu_failure 'The total should be  100*10*(1-0.3) '
END