------------------------------------------------------------------
--                                                              --
--  File:   BrokerVolume.SQL                                    --
--          Microsoft TPC-E Benchmark Kit Ver. 1.00             --
--         Copyright Microsoft, 2006, 2007                      --
--                                                              --
------------------------------------------------------------------
USE tpce
GO

IF EXISTS(  SELECT  name
            FROM    sysobjects
            WHERE   name    = 'BrokerVolumeFrame1' AND
                    type    = 'P')
DROP PROCEDURE  BrokerVolumeFrame1
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

-- BrokerVolumeFrame1 is responsible retrieving 
-- the total volume that could be potentially 
-- generated by each qualifying broker.
CREATE PROCEDURE    BrokerVolumeFrame1
                        @broker1        varchar( 100 ),  /* IN list of brokers */ 
                        @broker2        varchar( 100 ),
                        @broker3        varchar( 100 ),
                        @broker4        varchar( 100 ),
                        @broker5        varchar( 100 ),
                        @broker6        varchar( 100 ),
                        @broker7        varchar( 100 ),
                        @broker8        varchar( 100 ),
                        @broker9        varchar( 100 ),
                        @broker10       varchar( 100 ),
                        @broker11       varchar( 100 ),
                        @broker12       varchar( 100 ),
                        @broker13       varchar( 100 ),
                        @broker14       varchar( 100 ),
                        @broker15       varchar( 100 ),
                        @broker16       varchar( 100 ),
                        @broker17       varchar( 100 ),
                        @broker18       varchar( 100 ),
                        @broker19       varchar( 100 ),
                        @broker20       varchar( 100 ),
                        @broker21       varchar( 100 ),
                        @broker22       varchar( 100 ),
                        @broker23       varchar( 100 ),
                        @broker24       varchar( 100 ),
                        @broker25       varchar( 100 ),
                        @broker26       varchar( 100 ),
                        @broker27       varchar( 100 ),
                        @broker28       varchar( 100 ),
                        @broker29       varchar( 100 ),
                        @broker30       varchar( 100 ),
                        @broker31       varchar( 100 ),
                        @broker32       varchar( 100 ),
                        @broker33       varchar( 100 ),
                        @broker34       varchar( 100 ),
                        @broker35       varchar( 100 ),
                        @broker36       varchar( 100 ),
                        @broker37       varchar( 100 ),
                        @broker38       varchar( 100 ),
                        @broker39       varchar( 100 ),
                        @broker40       varchar( 100 ),
                        @sector_name    char( 30 )    /* IN sector's name */ 
AS
BEGIN
   --SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT

	BEGIN TRANSACTION

    SELECT  B_NAME,
            SUM(TR_QTY * TR_BID_PRICE)
    FROM    BROKER,
            TRADE_REQUEST,
            SECURITY,
            COMPANY,
            INDUSTRY,
            SECTOR
    WHERE   B_NAME      IN (@broker1,  @broker2,  @broker3,  @broker4,  @broker5,
                            @broker6,  @broker7,  @broker8,  @broker9,  @broker10,
                            @broker11, @broker12, @broker13, @broker14, @broker15,
                            @broker16, @broker17, @broker18, @broker19, @broker20,
                            @broker21, @broker22, @broker23, @broker24, @broker25,
                            @broker26, @broker27, @broker28, @broker29, @broker30,
                            @broker31, @broker32, @broker33, @broker34, @broker35,
                            @broker36, @broker37, @broker38, @broker39, @broker40) AND
            B_ID        = TR_B_ID   AND
            TR_S_SYMB   = S_SYMB    AND
            S_CO_ID     = CO_ID     AND
            CO_IN_ID    = IN_ID     AND
            IN_SC_ID    = SC_ID     AND
            SC_NAME     = @sector_name
    GROUP   BY B_NAME
    ORDER   BY  2  DESC
    OPTION (FORCE ORDER, LOOP JOIN)

    COMMIT TRANSACTION	
END