Back to OIM Explorer

dbo.QER_FTMethodForPRNode

Table FunctionSQL_TABLE_VALUED_FUNCTIONSandbox DB

Table Function.

Source: sandbox-db sys.sql_modules

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

SQL75 lines
1CREATE FUNCTION dbo.QER_FTMethodForPRNode(2  @PRNodes QBM_YSingleGUID READONLY3) RETURNS @erg TABLE(UID_OrgPR varchar(38) collate database_default,4UID_PWODecisionMethod varchar(38) collate database_default,5UID_PWODecisionMethod_old varchar(38) collate database_default6)7AS8BEGIN9  DECLARE @MaxAccProductGroupLevel int10  DECLARE @i int11  DECLARE @ProductNodeHasPWODecisionMethod QBM_YCursorBuffer12  INSERT INTO @ProductNodeHasPWODecisionMethod(UID1,13  UID2,14  UID3,15  UID4,16  UID5)17  SELECT18    b.UID_Org,19    b.UID_PWODecisionMethod,20    NULL,21    b.UID_AccProduct,22    b.UID_ParentOrg23  FROM @PRNodes p24  JOIN BaseTree b25    ON p.UID_SingleGuid = b.UID_Org26  WHERE27    b.UID_AccProduct > ' '28  UPDATE @ProductNodeHasPWODecisionMethod29  SET UID3 = a.UID_PWODecisionMethod30  FROM @ProductNodeHasPWODecisionMethod b31  JOIN AccProduct a32    ON b.UID4 = a.UID_AccProduct33  WHERE34    b.UID3 IS NULL35  SELECT @MaxAccProductGroupLevel = MAX(v.CountSteps)36  FROM QER_VACCProductGroupCollection v37  SELECT @i= 038  WHILE @i <= @MaxAccProductGroupLevel AND EXISTS(39  SELECT TOP 1 140  FROM @ProductNodeHasPWODecisionMethod b41  WHERE42    b.UID3 IS NULL)43  BEGIN44    UPDATE @ProductNodeHasPWODecisionMethod45    SET UID3 = gp.UID_PWODecisionMethod46    FROM @ProductNodeHasPWODecisionMethod b47    JOIN accproduct a48      ON b.UID4 = a.UID_AccProduct49    JOIN accproductGroup g50      ON a.uid_accproductGroup = g.uid_accproductGroup51    JOIN QER_VACCProductGroupCollection co52      ON co.UID_ACCProductGroup = g.UID_AccProductGroup AND co.CountSteps = @i AND co.UID_AccProductGroupParent > ' '53    JOIN AccProductGroup gp54      ON co.UID_AccProductGroupParent = gp.UID_AccProductGroup55    WHERE56      b.UID3 IS NULL AND gp.UID_PWODecisionMethod > ' '57    SELECT @i += 158  END59  UPDATE @ProductNodeHasPWODecisionMethod60  SET UID3 = h.UID_PWODecisionMethod61  FROM @ProductNodeHasPWODecisionMethod b62  JOIN PWOHelperBoardMethod h63    ON h.uid_OrgBO = b.UID564  WHERE65    b.UID3 IS NULL66  INSERT INTO @erg(UID_OrgPR,67  UID_PWODecisionMethod,68  UID_PWODecisionMethod_old)69  SELECT70    h.UID1 AS UID_OrgPR,71    UID3 AS UID_PWODecisionMethod_new,72    UID2 AS UID_PWODecisionMethod_old73  FROM @ProductNodeHasPWODecisionMethod h74  RETURN75END
Open raw exported source
SQL ยท Raw15 lines
1create function dbo.QER_FTMethodForPRNode (@PRNodes QBM_YSingleGUID readonly  ) returns @erg table( UID_OrgPR varchar(38) collate database_default , UID_PWODecisionMethod2 varchar(38) collate database_default  , UID_PWODecisionMethod_old varchar(38) collate database_default  ) as begin declare @MaxAccProductGroupLevel int3 declare @i int declare @ProductNodeHasPWODecisionMethod QBM_YCursorBuffer   insert into @ProductNodeHasPWODecisionMethod ( UID1  , UID2 , UID3  , UID44 , UID5 ) select b.UID_Org, b.UID_PWODecisionMethod , null, b.UID_AccProduct, b.UID_ParentOrg from @PRNodes p join BaseTree b on p.UID_SingleGuid = b.UID_Org5   where b.UID_AccProduct > ' ' update @ProductNodeHasPWODecisionMethod set UID3  = a.UID_PWODecisionMethod from @ProductNodeHasPWODecisionMethod b join6 AccProduct a on b.UID4  = a.UID_AccProduct  where b.UID3  is null  select @MaxAccProductGroupLevel = MAX(v.CountSteps) from QER_VACCProductGroupCollection7 v select @i= 0 while @i <= @MaxAccProductGroupLevel and exists (select top 1 1 from @ProductNodeHasPWODecisionMethod b where b.UID3  is null ) begin update8 @ProductNodeHasPWODecisionMethod set UID3  = gp.UID_PWODecisionMethod from @ProductNodeHasPWODecisionMethod b join accproduct a on b.UID4  = a.UID_AccProduct9 join accproductGroup g on a.uid_accproductGroup = g.uid_accproductGroup join QER_VACCProductGroupCollection co on co.UID_ACCProductGroup = g.UID_AccProductGroup10 and co.CountSteps = @i and co.UID_AccProductGroupParent > ' ' join AccProductGroup gp on co.UID_AccProductGroupParent = gp.UID_AccProductGroup where b.UID311  is null and gp.UID_PWODecisionMethod > ' ' select @i += 1 end update @ProductNodeHasPWODecisionMethod set UID3  = h.UID_PWODecisionMethod from @ProductNodeHasPWODecisionMethod12 b join PWOHelperBoardMethod h on h.uid_OrgBO = b.UID5   where b.UID3  is null insert into @erg(UID_OrgPR, UID_PWODecisionMethod, UID_PWODecisionMethod_old13) select h.UID1 as UID_OrgPR , UID3 as UID_PWODecisionMethod_new , UID2 as UID_PWODecisionMethod_old   from @ProductNodeHasPWODecisionMethod h return end14 15