USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS

CREATE procedure USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS
(
  @IDSET uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier
) 
as
begin
  set nocount on;

  declare @SQLTOEXEC nvarchar(max);

  declare @DBOBJECTNAME nvarchar(128);
  declare @DBOBJECTTYPE smallint;
  declare @CURRENTDATE datetime;

  set @CURRENTDATE = getdate()

  if @IDSET is not null
    begin
      if @IDSET = '00000000-0000-0000-0000-000000000001' or @IDSET = '00000000-0000-0000-0000-000000000002'
        begin
          declare @NODES as table (ID uniqueidentifier, ITERATION integer)
          declare @CONSTITUENTID as uniqueidentifier;

          select @CONSTITUENTID = CONSTITUENTID from dbo.APPUSER where ID = @CURRENTAPPUSERID;

          declare @ITERATION integer;
          set @ITERATION = 1;

          insert into @NODES (ID, ITERATION)
            select 
              POSITIONID,
              @ITERATION
            from dbo.ORGANIZATIONPOSITIONHOLDER
            where CONSTITUENTID = @CONSTITUENTID
            --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

            and cast(DATEFROM as date) < @CURRENTDATE
            --The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

            and (DATETO is null or dateadd(ms, -003, dateadd(d, 1, cast(cast(DATETO as date) as datetime))) < @CURRENTDATE);

          if @IDSET = '00000000-0000-0000-0000-000000000001' --Direct reports

            begin
              insert into @NODES 
                select ORGANIZATIONHIERARCHY.ID, @ITERATION + 1 
                from dbo.ORGANIZATIONHIERARCHY 
                where ORGANIZATIONHIERARCHY.PARENTID IN (select ID from @NODES where ITERATION = @ITERATION);
            end

          if @IDSET = '00000000-0000-0000-0000-000000000002' --Indirect reports

            begin
              declare @CONTINUE bit;
              set @CONTINUE = 1;
              while @CONTINUE = 1 
              begin
                insert into @NODES 
                  select ORGANIZATIONHIERARCHY.ID, @ITERATION + 1 
                  from dbo.ORGANIZATIONHIERARCHY 
                  where ORGANIZATIONHIERARCHY.PARENTID IN (select ID from @NODES where ITERATION = @ITERATION);

                if @@ROWCOUNT = 0 
                  set @CONTINUE = 0;

                set @ITERATION = @ITERATION + 1;
                --protect from runaway recursion

                if @ITERATION > 100 set @CONTINUE = 0;
              end
            end

          select OPH.ID
          from @NODES N
          inner join dbo.ORGANIZATIONPOSITION OP on N.ID = OP.ID
          inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH on OP.ID = OPH.POSITIONID
          where dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, OP.SITEID) = 1;

          return 0;
        end                                   
      else --Else this is REAL idset

        begin
          select @DBOBJECTNAME = DBOBJECTNAME, @DBOBJECTTYPE = OBJECTTYPE from dbo.IDSETREGISTER where ID = @IDSET;
          if @DBOBJECTTYPE = 1 set @DBOBJECTNAME = @DBOBJECTNAME + '()';
          else if @DBOBJECTTYPE = 2 set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @IDSET) + ''')';

          if @DBOBJECTNAME is not null
          begin
            set @SQLTOEXEC = 'select ID from ' + @DBOBJECTNAME + nchar(13);
            exec sp_executesql @SQLTOEXEC;
            return 0;
          end
        end
    end

  return 0;
end