USP_DATALIST_SPONSORSHIPLOCATIONHIERARCHY_CMS

      CREATE procedure [dbo].[USP_DATALIST_SPONSORSHIPLOCATIONHIERARCHY_CMS](
            @INCLUDEACTIVE bit = 1,
            @INCLUDEINACTIVE bit = 1,
            @INCLUDECLOSED bit = 1,
            @INCLUDEONLINEONLY bit = 0,
            @QUERYID uniqueidentifier
        )
        with execute as owner
        as       
        set nocount on;

        declare @FILTERTABLE as nvarchar(500);
            declare @IDSETREGISTERID uniqueidentifier = null;
            declare @SQL nvarchar(max);

            create table #LOCATIONS (
        ID uniqueidentifier,
        NAME nvarchar(100) collate DATABASE_DEFAULT,
        [TYPE] nvarchar(100) collate DATABASE_DEFAULT,
        [STATUS] nvarchar(8) collate DATABASE_DEFAULT,
        SPONSORSHIPREASONID uniqueidentifier null,
        REASON nvarchar(100) collate DATABASE_DEFAULT null,
        DESIGNATIONID uniqueidentifier null,
        DESIGNATION nvarchar(512) collate DATABASE_DEFAULT null,
        PARENTID uniqueidentifier null,
        SHOWPROCESSPAGE int,
        ALLOWMARKACTIVE int,
        ALLOWMARKINACTIVE int
        )

            insert #LOCATIONS
            execute dbo.USP_DATALIST_SPONSORSHIPLOCATIONHIERARCHY @INCLUDEACTIVE, @INCLUDEINACTIVE, @INCLUDECLOSED, @INCLUDEONLINEONLY

            select @IDSETREGISTERID = IDSETREGISTERID 
            from dbo.IDSETREGISTERADHOCQUERY 
            where ADHOCQUERYID = @QUERYID

            select @FILTERTABLE = 'dbo.' + IDSETREGISTER.DBOBJECTNAME + case IDSETREGISTER.OBJECTTYPE when 1 then '()' else '' end
            from IDSETREGISTER where IDSETREGISTER.ID = @IDSETREGISTERID      

            if @FILTERTABLE is not null
                begin

                    set @SQL = 'select loc.ID, loc.NAME, loc.TYPE,loc.STATUS,loc.REASON, loc.DESIGNATIONID,
                                       loc.DESIGNATION, dbo.UFN_SPONSORSHIPLOCATION_GETCMSPARENTID(loc.PARENTID) PARENTID, filterloc.OPPORTUNITYCOUNTFORQUERY
                                from 
                                      (select COUNT(query.id) OPPORTUNITYCOUNTFORQUERY, query.PARENTID
                                            from            
                                                  (select filter.id, dbo.UFN_SPONSORSHIPLOCATION_GETCMSPARENTID(sop.SPONSORSHIPLOCATIONID) as PARENTID
                                                        from '+ @FILTERTABLE +' FILTER
                                                        inner join dbo.SPONSORSHIPOPPORTUNITY sop on  sop.ID = FILTER.ID
                                                   ) query
                                            group by query.PARENTID
                                      ) FilterLoc
                                inner join #LOCATIONS loc on FilterLoc.PARENTID = loc.ID 
                                order by loc.NAME'


                    exec sp_executesql @SQL

                end
            else
                 begin
                  select  
                      ID,
                      NAME,
                      [TYPE],
                      [STATUS],
                      REASON,
                      DESIGNATIONID,
                      DESIGNATION,
                      dbo.UFN_SPONSORSHIPLOCATION_GETCMSPARENTID(PARENTID) as PARENTID,
                      -1
                      from 
                      #LOCATIONS
                      order by
                      NAME
                  end

            drop table #LOCATIONS