Back to OIM Explorer

dbo.ARS_FTPersonFromEdsva

Table FunctionSQL_TABLE_VALUED_FUNCTIONSandbox DB

Table Function.

Source: sandbox-db sys.sql_modules

Source size: 2.001 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

SQL102 lines
1CREATE FUNCTION dbo.ARS_FTPersonFromEdsva(2  @Ownerlist nvarchar(max)3) RETURNS @erg TABLE(UID_Person varchar(38) collate database_default4)5AS6BEGIN7  DECLARE @Objects TABLE(element nvarchar(1024) collate database_default,8  ElementType int DEFAULT 0)9  DECLARE @SumDistinct int10  DECLARE @t1 QBM_YParameterList11  DECLARE @t2 QBM_YParameterList12  DECLARE @t3 QBM_YParameterList13  DECLARE @t4 QBM_YParameterList14  INSERT INTO @objects(element,15  ElementType)16  SELECT17    e.value,18  CASE19    WHEN e.value LIKE 'CN=%' THEN20  121  ELSE 222  END AS ElementType23  FROM string_split(@Ownerlist,24  nchar(7)) e25  WHERE26    e.value > ' '27  SELECT @SumDistinct = sum(DISTINCT o.ElementType)28  FROM @Objects o29  IF @SumDistinct IN(1,30  3)31  BEGIN32    INSERT INTO @t1(Parameter1)33    SELECT a.UID_Person34    FROM @Objects o35    JOIN ADSAccount a36      ON o.element = a.DistinguishedName37    WHERE38      o.ElementType = 139  END40  IF @SumDistinct IN(2,41  3)42  BEGIN43    INSERT INTO @t2(Parameter1)44    SELECT a.UID_Person45    FROM @Objects o46    JOIN ADSAccount a47      ON o.element = a.ObjectSID48    WHERE49      o.ElementType = 250  END51  IF @SumDistinct IN(1,52  3)53  BEGIN54    INSERT INTO @t3(Parameter1)55    SELECT a.UID_Person56    FROM @Objects o57    JOIN ADSGroup g58      ON o.element = g.DistinguishedName59    JOIN ADSVAccountInADSGroup aig60      ON g.UID_ADSGroup = aig.UID_ADSGroup AND aig.XOrigin > 0 AND aig.XIsInEffect = 161    JOIN ADSAccount a62      ON aig.UID_ADSAccount = a.UID_ADSAccount63    WHERE64      o.ElementType = 165  END66  IF @SumDistinct IN(2,67  3)68  BEGIN69    INSERT INTO @t4(Parameter1)70    SELECT a.UID_Person71    FROM @Objects o72    JOIN ADSGroup g73      ON o.element = g.ObjectSID74    JOIN ADSVAccountInADSGroup aig75      ON g.UID_ADSGroup = aig.UID_ADSGroup AND aig.XOrigin > 0 AND aig.XIsInEffect = 176    JOIN ADSAccount a77      ON aig.UID_ADSAccount = a.UID_ADSAccount78    WHERE79      o.ElementType = 280  END81  INSERT INTO @erg(UID_Person)82  SELECT t.Parameter183  FROM @t1 t84  WHERE85    t.Parameter1 > ' '86  UNION87  SELECT t.Parameter188  FROM @t2 t89  WHERE90    t.Parameter1 > ' '91  UNION92  SELECT t.Parameter193  FROM @t3 t94  WHERE95    t.Parameter1 > ' '96  UNION97  SELECT t.Parameter198  FROM @t4 t99  WHERE100    t.Parameter1 > ' ' endLabel:101  RETURN102END
Open raw exported source
SQL ยท Raw14 lines
1create function dbo.ARS_FTPersonFromEdsva(@Ownerlist nvarchar(max) ) returns @erg table ( UID_Person varchar(38) collate database_default ) AS begin declare2 @Objects table (element nvarchar(1024) collate database_default  , ElementType int default 0   ) declare @SumDistinct int declare @t1 QBM_YParameterList3 declare @t2 QBM_YParameterList declare @t3 QBM_YParameterList declare @t4 QBM_YParameterList  insert into @objects (element, ElementType) select e.value4, case when e.value like 'CN=%' then 1 else 2 end as ElementType from string_split( @Ownerlist, nchar(7)) e where e.value > ' ' select @SumDistinct = sum5(distinct o.ElementType) from @Objects o  if @SumDistinct in (1,3) begin insert into @t1(Parameter1) select a.UID_Person from @Objects o join ADSAccount6 a on o.element = a.DistinguishedName where o.ElementType = 1 end if @SumDistinct in (2,3) begin insert into @t2(Parameter1) select a.UID_Person from @Objects7 o join ADSAccount a on o.element = a.ObjectSID where o.ElementType = 2 end if @SumDistinct in (1,3) begin insert into @t3(Parameter1) select a.UID_Person8 from @Objects o join ADSGroup g on o.element = g.DistinguishedName join ADSVAccountInADSGroup aig on g.UID_ADSGroup = aig.UID_ADSGroup and aig.XOrigin9 > 0 and aig.XIsInEffect = 1 join ADSAccount a on aig.UID_ADSAccount = a.UID_ADSAccount where o.ElementType = 1 end if @SumDistinct in (2,3) begin insert10 into @t4(Parameter1) select a.UID_Person from @Objects o join ADSGroup g on o.element = g.ObjectSID join ADSVAccountInADSGroup aig on g.UID_ADSGroup =11 aig.UID_ADSGroup and aig.XOrigin > 0 and aig.XIsInEffect = 1 join ADSAccount a on aig.UID_ADSAccount = a.UID_ADSAccount where o.ElementType = 2 end insert12 into @erg(UID_Person) select t.Parameter1 from @t1 t where t.Parameter1 > ' ' union select t.Parameter1 from @t2 t where t.Parameter1 > ' ' union select13 t.Parameter1 from @t3 t where t.Parameter1 > ' ' union select t.Parameter1 from @t4 t where t.Parameter1 > ' ' endLabel: return end 14