USP_SEGMENTGROUP_GETCOUNT

CREATE procedure dbo.USP_SEGMENTGROUP_GETCOUNT
(
    @SEGMENTGROUPID uniqueidentifier
)
with execute as owner
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' or SMARTFIELD.SMARTFIELDCATALOGID is null)
    )

    declare @TOTALCOUNT int;
    declare @COUNT int;

    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);

            if LEN(@TABLENAME) > 0
            begin
                set @SQL = 'select @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)
            end
            else
            begin
                set @SQL = 'select @COUNT = COUNT(<%VIEWNAME%>.ID) from dbo.<%VIEWNAME%>;';
                set @SQL = REPLACE(@SQL, '<%VIEWNAME%>', @VIEWNAME);
            end

            exec sp_executesql @SQL, N'@COUNT int output', @COUNT output;
            set @TOTALCOUNT = @TOTALCOUNT + @COUNT;

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

    select @TOTALCOUNT;

    return 0;