USP_SEGMENTGROUP_GETAVGNUMBEROFGIFTS

create procedure dbo.USP_SEGMENTGROUP_GETAVGNUMBEROFGIFTS
(
    @SEGMENTGROUPID uniqueidentifier
)
as
    set nocount on;

    declare @TABLENAME nvarchar(255);
    declare @VIEWNAME nvarchar(255);
    declare @VALUECOLUMNNAME nvarchar(255);
    declare TABLECURSOR cursor local fast_forward for

    (
        select
            TABLECATALOG.TABLENAME,
            IDSETREGISTER.DBOBJECTNAME,
            SMARTFIELD.VALUECOLUMNNAME
        from
            dbo.MKTSEGMENT
        left outer join dbo.IDSETREGISTER on IDSETREGISTER.ID = MKTSEGMENT.IDSETREGISTERID
        left outer join dbo.MKTGROUPSEGMENTS on MKTGROUPSEGMENTS.SEGMENTID = MKTSEGMENT.ID
        left outer join dbo.MKTSEGMENTSELECTION on MKTSEGMENTSELECTION.SEGMENTID = MKTSEGMENT.ID
        left outer join dbo.SMARTFIELDVALUEGROUPIDSETREGISTER on SMARTFIELDVALUEGROUPIDSETREGISTER.IDSETREGISTERID = MKTSEGMENTSELECTION.SELECTIONID
        left outer join dbo.SMARTFIELDVALUEGROUP on SMARTFIELDVALUEGROUP.ID = SMARTFIELDVALUEGROUPIDSETREGISTER.SMARTFIELDVALUEGROUPID
        left outer join dbo.SMARTFIELD on SMARTFIELD.ID = SMARTFIELDVALUEGROUP.SMARTFIELDID
        left outer join dbo.TABLECATALOG on TABLECATALOG.ID = SMARTFIELD.TABLECATALOGID
        where
            MKTGROUPSEGMENTS.SEGMENTGROUPID = @SEGMENTGROUPID
            and SMARTFIELD.SMARTFIELDCATALOGID = '7F0415B0-8077-4E7E-BEF4-92146799EFC3'
    )

    declare @TOTALGIFTS int;
    declare @VALUE int;
    declare @TOTALCOUNT int;
    declare @COUNT int;

    set @TOTALGIFTS = 0;
    set @VALUE = 0;
    set @TOTALCOUNT = 0;
    set @COUNT = 0;

    open TABLECURSOR;
    fetch next from TABLECURSOR into @TABLENAME, @VIEWNAME, @VALUECOLUMNNAME;
    if (@@FETCH_STATUS = 0)
    begin
        while (@@FETCH_STATUS = 0)
        begin
            declare @SQL nvarchar(max);

            set @SQL = 'select @VALUE = coalesce(SUM(coalesce(<%TABLENAME%>.<%VALUECOLUMNNAME%>, 0)), 0), @COUNT = COUNT(<%TABLENAME%>.ID) from dbo.<%TABLENAME%> inner join dbo.<%VIEWNAME%> on <%VIEWNAME%>.ID = <%TABLENAME%>.ID;';
            set @SQL = REPLACE(REPLACE(REPLACE(@SQL, '<%VIEWNAME%>', @VIEWNAME), '<%TABLENAME%>', @TABLENAME), '<%VALUECOLUMNNAME%>', @VALUECOLUMNNAME)
            exec sp_executesql @SQL, N'@VALUE int output, @COUNT int output', @VALUE output, @COUNT output;

            set @TOTALGIFTS = @TOTALGIFTS + @VALUE;
            set @TOTALCOUNT = @TOTALCOUNT + @COUNT;

            fetch next from TABLECURSOR into @TABLENAME, @VIEWNAME, @VALUECOLUMNNAME;
        end
        close TABLECURSOR;
        deallocate TABLECURSOR;
    end

    if @TOTALCOUNT > 0
        select @TOTALGIFTS / @TOTALCOUNT;
    else
        select @TOTALGIFTS;

    return 0;