Back to OIM Explorer

dbo.QER_FTPersonHeadCirc_calc

Table FunctionSQL_TABLE_VALUED_FUNCTIONSandbox DB

Table Function.

Source: sandbox-db sys.sql_modules

Source size: 2.642 characters

Interpretation

  • Database function. Usually supports views, validation, or calculated predicates; look at referenced-by entries for callers.

Relations

  • No extracted relations.

Typed Edges

  • No typed edges extracted for this source.

References

  • No direct source references extracted.

Referenced By

Complete Source

SQL95 lines
1CREATE FUNCTION dbo.QER_FTPersonHeadCirc_calc(2  @soFar dbo.QER_YPersonHeadCircular READONLY3) RETURNS @erg TABLE(UID_Person varchar(38) collate database_default NOT NULL,4UID_PersonHead varchar(38) collate database_default NOT NULL,5IsDirectReportsContained BIT DEFAULT 0,6IsDirectReportsOnly BIT DEFAULT 0,7IsHeadOrgContained BIT DEFAULT 0,8IsDelegationContained BIT DEFAULT 09)10  WITH schemabinding11AS12BEGIN13  DECLARE @work dbo.QER_YPersonHeadCircular14  DECLARE @lauf int15  INSERT INTO @work(UID_Person,16  UID_PersonHead,17  IsDirectReportsContained,18  IsHeadOrgContained,19  IsCircular,20  IsDelegationContained,21  IsDirectReportsOnly)22  SELECT23    UID_Person,24    UID_PersonHead,25    IsDirectReportsContained,26    IsHeadOrgContained,27    IsCircular,28    IsDelegationContained,29    IsDirectReportsOnly30  FROM @soFar31  SELECT @lauf = 132  WHILE @lauf > 033  BEGIN34    INSERT INTO @work(uid_person,35    uid_personhead,36    IsDirectReportsContained,37    IsHeadOrgContained,38    IsCircular,39    IsDelegationContained,40    IsDirectReportsOnly)41    SELECT42      l.uid_person,43      h.uid_personhead,44      max(convert(int, l.IsDirectReportsContained | h.IsDirectReportsContained)) AS IsDirectReportsContained,45      max(convert(int, l.IsHeadOrgContained | h.IsHeadOrgContained)) AS IsHeadOrgContained,46      0 AS IsCircular,47      max(convert(int, l.IsDelegationContained | h.IsDelegationContained)) AS IsDelegationContained,48      Min(convert(int, l.IsDirectReportsOnly & h.IsDirectReportsOnly)) AS IsDirectReportsOnly49    FROM @work l50    JOIN @work h51      ON l.UID_PersonHead = h.UID_Person52    GROUP BY l.UID_Person,53    h.UID_PersonHead54    HAVING NOT EXISTS(55    SELECT TOP 1 156    FROM @work e57    WHERE58      e.UID_Person = l.UID_Person AND e.UID_PersonHead = h.uid_personhead) AND l.UID_Person <> h.UID_PersonHead59    SELECT @lauf = @@ROWCOUNT60  END61  UPDATE @work62  SET IsCircular = 1,63  IsDelegationContained = l.IsDelegationContained | h.IsDelegationContained,64  IsDirectReportsContained = l.IsDirectReportsContained | h.IsDirectReportsContained,65  IsDirectReportsOnly = l.IsDirectReportsOnly & h.IsDirectReportsOnly,66  IsHeadOrgContained = l.IsHeadOrgContained | h.IsHeadOrgContained67  FROM @work l68  JOIN(69  SELECT70    e.UID_PersonHead,71    e.UID_Person,72    e.IsDelegationContained,73    e.IsDirectReportsContained,74    e.IsDirectReportsOnly,75    e.IsHeadOrgContained76  FROM @work e) AS h77    ON l.uid_person = h.uid_personhead AND l.uid_personhead = h.uid_person78  INSERT INTO @erg(UID_Person,79  UID_PersonHead,80  IsDirectReportsContained,81  IsDirectReportsOnly,82  IsDelegationContained,83  IsHeadOrgContained)84  SELECT85    UID_Person,86    UID_PersonHead,87    IsDirectReportsContained,88    IsDirectReportsOnly,89    IsDelegationContained,90    IsHeadOrgContained91  FROM @work w92  WHERE93    w.IsCircular = 194  RETURN95END
Open raw exported source
SQL ยท Raw18 lines
1create function dbo.QER_FTPersonHeadCirc_calc(@soFar dbo.QER_YPersonHeadCircular readonly ) returns @erg table ( UID_Person varchar(38) collate database_default2 Not Null , UID_PersonHead varchar(38) collate database_default Not Null   , IsDirectReportsContained bit default 0 , IsDirectReportsOnly bit default 03  , IsHeadOrgContained bit default 0 , IsDelegationContained bit default 0  ) with schemabinding as begin declare @work dbo.QER_YPersonHeadCircular declare4 @lauf int insert into @work( UID_Person , UID_PersonHead , IsDirectReportsContained , IsHeadOrgContained  , IsCircular , IsDelegationContained , IsDirectReportsOnly5  ) select UID_Person , UID_PersonHead , IsDirectReportsContained , IsHeadOrgContained  , IsCircular , IsDelegationContained , IsDirectReportsOnly from6 @soFar select @lauf = 1 while @lauf > 0 begin insert into @work(uid_person, uid_personhead , IsDirectReportsContained , IsHeadOrgContained  , IsCircular7 , IsDelegationContained , IsDirectReportsOnly  ) select l.uid_person, h.uid_personhead , max(convert(int, l.IsDirectReportsContained | h.IsDirectReportsContained8)) as IsDirectReportsContained , max(convert(int, l.IsHeadOrgContained | h.IsHeadOrgContained)) as IsHeadOrgContained      , 0 as IsCircular , max(convert9(int, l.IsDelegationContained | h.IsDelegationContained )) as IsDelegationContained , Min(convert(int, l.IsDirectReportsOnly & h.IsDirectReportsOnly)) 10as IsDirectReportsOnly  from @work l join @work h on l.UID_PersonHead = h.UID_Person group by l.UID_Person, h.UID_PersonHead having not exists (select 11top 1 1 from @work e where e.UID_Person = l.UID_Person and e.UID_PersonHead = h.uid_personhead ) and l.UID_Person <> h.UID_PersonHead select @lauf = @@ROWCOUNT12 end update @work set IsCircular = 1 , IsDelegationContained = l.IsDelegationContained | h.IsDelegationContained , IsDirectReportsContained = l.IsDirectReportsContained13 | h.IsDirectReportsContained , IsDirectReportsOnly = l.IsDirectReportsOnly & h.IsDirectReportsOnly , IsHeadOrgContained = l.IsHeadOrgContained | h.IsHeadOrgContained14 from @work l join (select e.UID_PersonHead, e.UID_Person , e.IsDelegationContained, e.IsDirectReportsContained, e.IsDirectReportsOnly, e.IsHeadOrgContained15 from @work e ) as h on l.uid_person = h.uid_personhead and l.uid_personhead = h.uid_person insert into @erg(UID_Person, UID_PersonHead, IsDirectReportsContained16, IsDirectReportsOnly , IsDelegationContained, IsHeadOrgContained  ) select UID_Person, UID_PersonHead, IsDirectReportsContained, IsDirectReportsOnly ,17 IsDelegationContained, IsHeadOrgContained  from @work w where w.IsCircular = 1    return end 18