USP_REPORT_SPONSORSHIP

            create procedure dbo.USP_REPORT_SPONSORSHIP (
                @DIMENSION1 as nvarchar(100) = null,
                @DIMENSION1VALUE as nvarchar(255) = null,
                @DIMENSION1VALUE3 as nvarchar(255) = null,
                @DIMENSION2 as nvarchar(100) = null,
                @DIMENSION2VALUE as nvarchar(255) = null,
                @DIMENSION2VALUE3 as nvarchar(255) = null,
                @DIMENSION3 as nvarchar(100) = null,
                @DIMENSION3VALUE as nvarchar(255) = null,
                @DIMENSION3VALUE3 as nvarchar(255) = null
                )
            with execute as owner
            as
            set nocount on;

            declare @SQLTOEXEC nvarchar(max);
            declare @FROMCLAUSE nvarchar(4000);
            declare @COLUMNSELECT nvarchar(1000);
            declare @WHERECLAUSE nvarchar(1000);
            declare @REVENUEJOIN nvarchar(500);
            declare @GROUPBYCLAUSE nvarchar(500);
            declare @SELECT nvarchar(1000);
            declare @FROM nvarchar(1000);
            declare @COLUMN nvarchar(1000);
            declare @WHERE nvarchar(1000);
            declare @LOCATIONCOUNT tinyint = 0;
            declare @LOCATIONTYPEENUM tinyint = 2;
            declare @DATECOLTEMP nvarchar(800);
            declare @DIMENSIONCOLUMNS nvarchar(100) = 'DIMENSION1';
            declare @RESULTSELECTION nvarchar(2000);

            set @FROMCLAUSE = 'from dbo.SPONSORSHIP SP '
            set @WHERECLAUSE = 'where SP.STATUSCODE=1 '

            if @DIMENSION1 in (12,13,14,5) OR @DIMENSION2 in (12,13,14,5) OR @DIMENSION3 in (12,13,14,5)
            begin
                set @REVENUEJOIN = ' inner join dbo.REVENUESPLIT SPLT on SPLT.ID = SP.REVENUESPLITID ' +
                                   ' inner join dbo.REVENUE RV on RV.ID = SPLT.REVENUEID ';
                --

                -- only join to the development function table to get original channel, 

                -- appeal and frequency values if it exists

                --

                if @DIMENSION1 in (12,13,14) or  @DIMENSION2 in (12,13,14) or  @DIMENSION3 in (12,13,14)
                begin
                    set @REVENUEJOIN = @REVENUEJOIN + 'left outer join RECURRINGGIFTDEVELOPMENTFUNCTIONHISTORY RGDFH ON RGDFH.REVENUEID = RV.ID and RGDFH.SEQUENCE = 0 ';
                end
            end

            if @REVENUEJOIN is not null
            begin
                 set @FROMCLAUSE =  @FROMCLAUSE + @REVENUEJOIN;
            end

            set @COLUMNSELECT = 'select 0 DATECOL, 0 YEARCOL';

            -- count how many dimension parameters have location type specified

            if @DIMENSION1 = @LOCATIONTYPEENUM
            begin
                set @LOCATIONCOUNT = @LOCATIONCOUNT + 1;
            end

            if @DIMENSION2 is not null and @DIMENSION2 = @LOCATIONTYPEENUM
            begin
                set @LOCATIONCOUNT = @LOCATIONCOUNT + 1;
            end

            if @DIMENSION3 is not null and @DIMENSION3 = @LOCATIONTYPEENUM
            begin
                set @LOCATIONCOUNT = @LOCATIONCOUNT + 1;
            end

            -- only process dimension if not null and not Location Type

            if @DIMENSION1 is not null and @DIMENSION1 <> @LOCATIONTYPEENUM
            begin
                exec dbo.USP_REPORT_PROCESSDIMENSION 
                    @DIMENSION1, 
                    '@DIMENSION1VALUE', 
                    @FROM output, 
                    @COLUMN output, 
                    @WHERE output, 
                    'DIMENSION1'

                if @FROM is not null
                begin
                    set @FROMCLAUSE = @FROMCLAUSE + @FROM;
                end
                set @COLUMNSELECT = @COLUMNSELECT + @COLUMN;
                set @WHERECLAUSE += @WHERE + ' and @DIMENSION1VALUE3 is null';
                set @GROUPBYCLAUSE = 'DIMENSION1';
                --

                -- set these to null for the next row

                --

                set @FROM = '';
                set @COLUMN = '';
                set @WHERE = '';
            end

            -- only process dimension if it's not null and is not Location Type

            if @DIMENSION2 is not null
            begin 
                set @DIMENSIONCOLUMNS = @DIMENSIONCOLUMNS + ', DIMENSION2';
                if @DIMENSION2 <> @LOCATIONTYPEENUM
                begin
                    exec dbo.USP_REPORT_PROCESSDIMENSION
                        @DIMENSION2, 
                        '@DIMENSION2VALUE', 
                        @FROM output, 
                        @COLUMN output, 
                        @WHERE output, 
                        'DIMENSION2'

                    -- for channel and appeal from clause is not used.

                    if @FROM is not null
                    begin
                        set @FROMCLAUSE = @FROMCLAUSE + @FROM;
                    end
                    set @COLUMNSELECT =  @COLUMNSELECT + @COLUMN;
                    set @WHERECLAUSE =  @WHERECLAUSE + @WHERE + ' and @DIMENSION2VALUE3 is null';
                    if @GROUPBYCLAUSE is not null
                    begin
                        set @GROUPBYCLAUSE = @GROUPBYCLAUSE + ', DIMENSION2';
                    end
                    else
                    begin
                        set @GROUPBYCLAUSE = 'DIMENSION2';
                    end

                    --

                    -- set these to null for the next row

                    --

                    set @FROM = '';
                    set @COLUMN = '';
                    set @WHERE = '';
                end
            end
            else
            begin
                set @WHERECLAUSE =  @WHERECLAUSE + ' and @DIMENSION2VALUE is null and @DIMENSION2VALUE3 is null';
            end
            --

            if @DIMENSION3 is not null 
            begin
                set @DIMENSIONCOLUMNS = @DIMENSIONCOLUMNS + ', DIMENSION3';
                if @DIMENSION3 <> @LOCATIONTYPEENUM
                begin
                    exec dbo.USP_REPORT_PROCESSDIMENSION
                        @DIMENSION3,
                        '@DIMENSION3VALUE', 
                        @FROM output, 
                        @COLUMN output, 
                        @WHERE output, 
                        'DIMENSION3'

                    -- for channel and appeal from clause is not used.

                    if @FROM is not null
                    begin
                        set @FROMCLAUSE = @FROMCLAUSE + @FROM;
                    end

                    set @COLUMNSELECT =  @COLUMNSELECT + @COLUMN;
                    set @WHERECLAUSE =  @WHERECLAUSE + @WHERE + ' and @DIMENSION3VALUE3 is null';
                    if @GROUPBYCLAUSE is not null
                    begin
                        set @GROUPBYCLAUSE = @GROUPBYCLAUSE + ', DIMENSION3';
                    end
                    else
                    begin
                        set @GROUPBYCLAUSE = 'DIMENSION3';
                    end
                end
            end
            else
            begin
                set @WHERECLAUSE =  @WHERECLAUSE + ' and @DIMENSION3VALUE is null and @DIMENSION3VALUE3 is null';
            end

            -- handle processing of location types

            If @LOCATIONCOUNT > 0
            begin
                exec dbo.USP_REPORT_PROCESSLOCATIONTYPE_DIMENSION
                @LOCATIONCOUNT, 
                @DIMENSION1, 
                @DIMENSION2,
                @DIMENSION3,
                @COLUMNSELECT,
                @FROMCLAUSE,
                @WHERECLAUSE,
                @GROUPBYCLAUSE,
                @SQLTOEXEC output,
                'count(*) CNT'
            end
            else
            begin

                set @SELECT = 'select count(*) CNT, DATECOL, YEARCOL, '+ @GROUPBYCLAUSE;

                -- construct the sql query


                set @SQLTOEXEC = @SELECT + ' from (' + @COLUMNSELECT + ' ' + @FROMCLAUSE + ' ' + @WHERECLAUSE + ') as QRY group by DATECOL, YEARCOL, ' + @GROUPBYCLAUSE;

            end    
               /*   
            set @DATECOLTEMP = dbo.UFN_REPORT_BUILD_DATECOL_TABLE_STRING(@DATETYPE)

            -- append to the query the code that handles  blanc column values
            set @RESULTSELECTION =' delete from #DATECOL where DATECOL+YEARCOL in (select DATECOL+YEARCOL from #RESULT) ' +
                             ' insert into #RESULT (DATECOL, YEARCOL, CNT, ' + @DIMENSIONCOLUMNS + ') ' +
                             ' select distinct DATEC , YEARC , 0, ' + @DIMENSIONCOLUMNS +
                             ' from (select #DATECOL.DATECOL as DATEC, #DATECOL.YEARCOL as YEARC, #RESULT.* from #DATECOL cross join #RESULT) as CROSSJOIN ' +
                             ' select CNT, DATECOL, YEARCOL, ' + @DIMENSIONCOLUMNS + ' from #RESULT '  

            set @SQLTOEXEC = @DATECOLTEMP + ' select * into #RESULT from (' +@SQLTOEXEC + ') as RES ' + @RESULTSELECTION
*/

            -- execute the query

            exec sp_executesql @SQLTOEXEC,
                               N'@DIMENSION1VALUE nvarchar(255), @DIMENSION2VALUE nvarchar(255), @DIMENSION3VALUE nvarchar(255), @DIMENSION1VALUE3 nvarchar(255), @DIMENSION2VALUE3 nvarchar(255), @DIMENSION3VALUE3 nvarchar(255)',
                               @DIMENSION1VALUE=@DIMENSION1VALUE, @DIMENSION2VALUE=@DIMENSION2VALUE, @DIMENSION3VALUE=@DIMENSION3VALUE, @DIMENSION1VALUE3=@DIMENSION1VALUE3, @DIMENSION2VALUE3=@DIMENSION2VALUE3, @DIMENSION3VALUE3=@DIMENSION3VALUE3;