Back to OIM Explorer

dbo.QER_FGIPWOSubMethodHasCycle

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 3.617 characters

Interpretation

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

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FCVStringToList source text reference
  • references source dbo.QBM_FCVStringToListSplitted source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL132 lines
1CREATE FUNCTION dbo.QER_FGIPWOSubMethodHasCycle(2  @UID_PWODecisionSubMethod varchar(38),3  @UsePositiveSteps BIT,4  @UseNegativeSteps BIT,5  @UseEscalationSteps BIT,6  @UseDirectSteps BIT7) RETURNS nvarchar(10008)9AS10BEGIN11  DECLARE @lauf int12  DECLARE @DirectSteps nvarchar(1000)13  DECLARE @FlowShell QBM_YMNTable14  DECLARE @DirectHelp QBM_YSingleGUID15  DECLARE @erg nvarchar(1000)16  SELECT @erg = ''17  IF @UseDirectSteps = 118  BEGIN19    INSERT INTO @DirectHelp(UID_SingleGuid,20    IntProperty)21    SELECT22      s.UID_PWODecisionStep,23      a.ParameterName24    FROM PWODecisionStep s CROSS apply dbo.QBM_FCVStringToListSplitted(@DirectSteps,25    nchar(7),26    0,27    0,28    N '=') a29    WHERE30      s.UID_PWODecisionSubMethod = @UID_PWODecisionSubMethod AND s.DirectSteps > ' '31  END32  IF @UsePositiveSteps = 133  BEGIN34    INSERT INTO @FlowShell(UID_Element1,35    UID_Element2)36    SELECT37      DISTINCT s.UID_PWODecisionStep,38      n.UID_PWODecisionStep39    FROM pwodecisionstep s40    JOIN pwodecisionstep n41      ON s.UID_PWODecisionSubMethod = @UID_PWODecisionSubMethod AND n.UID_PWODecisionSubMethod = @UID_PWODecisionSubMethod AND s.PositiveSteps <> 0 AND42    s.UID_PWODecisionStep <> n.UID_PWODecisionStep AND s.levelnumber + s.PositiveSteps = n.levelnumber43  END44  IF @UseNegativeSteps = 145  BEGIN46    INSERT INTO @FlowShell(UID_Element1,47    UID_Element2)48    SELECT49      DISTINCT s.UID_PWODecisionStep,50      n.UID_PWODecisionStep51    FROM pwodecisionstep s52    JOIN pwodecisionstep n53      ON s.UID_PWODecisionSubMethod = @UID_PWODecisionSubMethod AND n.UID_PWODecisionSubMethod = @UID_PWODecisionSubMethod AND s.NegativeSteps <> 0 AND54    s.UID_PWODecisionStep <> n.UID_PWODecisionStep AND s.levelnumber + s.NegativeSteps = n.levelnumber55    WHERE56      NOT EXISTS(57    SELECT TOP 1 158    FROM @FlowShell e59    WHERE60      e.UID_Element1 = s.UID_PWODecisionStep AND e.UID_Element2 = n.UID_PWODecisionStep)61  END62  IF @UseEscalationSteps = 163  BEGIN64    INSERT INTO @FlowShell(UID_Element1,65    UID_Element2)66    SELECT67      DISTINCT s.UID_PWODecisionStep,68      n.UID_PWODecisionStep69    FROM pwodecisionstep s70    JOIN pwodecisionstep n71      ON s.UID_PWODecisionSubMethod = @UID_PWODecisionSubMethod AND n.UID_PWODecisionSubMethod = @UID_PWODecisionSubMethod AND s.EscalationSteps <>72    0 AND s.UID_PWODecisionStep <> n.UID_PWODecisionStep AND s.levelnumber + s.EscalationSteps = n.levelnumber73    WHERE74      NOT EXISTS(75    SELECT TOP 1 176    FROM @FlowShell e77    WHERE78      e.UID_Element1 = s.UID_PWODecisionStep AND e.UID_Element2 = n.UID_PWODecisionStep)79  END80  IF @UseDirectSteps = 181  BEGIN82    INSERT INTO @FlowShell(UID_Element1,83    UID_Element2)84    SELECT85      DISTINCT s.UID_PWODecisionStep,86      n.UID_PWODecisionStep87    FROM pwodecisionstep s88    JOIN @DirectHelp h89      ON s.uid_pwodecisionstep = h.UID_SingleGuid AND s.UID_PWODecisionSubMethod = @UID_PWODecisionSubMethod AND s.Directsteps > ' '90    JOIN pwodecisionstep n91      ON n.UID_PWODecisionSubMethod = @UID_PWODecisionSubMethod AND s.UID_PWODecisionStep <> n.UID_PWODecisionStep AND s.levelnumber + h.IntProperty92    = n.levelnumber93    WHERE94      NOT EXISTS(95    SELECT TOP 1 196    FROM @FlowShell e97    WHERE98      e.UID_Element1 = s.UID_PWODecisionStep AND e.UID_Element2 = n.UID_PWODecisionStep)99  END100  SELECT @lauf = 1101  WHILE @lauf > 0102  BEGIN103    INSERT INTO @FlowShell(UID_Element1,104    UID_Element2)105    SELECT106      DISTINCT s.UID_Element1,107      n.UID_Element2108    FROM @FlowShell s109    JOIN @FlowShell n110      ON s.UID_Element2 = n.UID_Element1111    WHERE112      NOT EXISTS(113    SELECT TOP 1 1114    FROM @FlowShell e115    WHERE116      e.UID_Element1 = s.UID_Element1 AND e.UID_Element2 = n.UID_Element2)117    SELECT @lauf = @@rowcount118  END119  IF EXISTS(120    SELECT TOP 1 1121    FROM @FlowShell122    WHERE123      UID_Element1 = UID_Element2)124  BEGIN125    SELECT TOP 1 @erg = fs.UID_Element1126    FROM @FlowShell fs127    WHERE128      fs.UID_Element1 = fs.UID_Element2129  END130  ende:131  RETURN(@erg)132END
Open raw exported source
SQL ยท Raw24 lines
1   create   function dbo.QER_FGIPWOSubMethodHasCycle ( @UID_PWODecisionSubMethod varchar(38) , @UsePositiveSteps bit , @UseNegativeSteps bit , @UseEscalationSteps2 bit , @UseDirectSteps bit ) returns nvarchar(1000) as begin declare @lauf int declare @DirectSteps nvarchar(1000)  declare @FlowShell QBM_YMNTable  declare3 @DirectHelp QBM_YSingleGUID  declare @erg nvarchar(1000) select @erg = ''  if @UseDirectSteps = 1 begin  insert into @DirectHelp (UID_SingleGuid , IntProperty4  ) select s.UID_PWODecisionStep, a.ParameterName from PWODecisionStep s cross apply dbo.QBM_FCVStringToListSplitted(@DirectSteps, nchar(7), 0, 0 , N'='5) a where s.UID_PWODecisionSubMethod = @UID_PWODecisionSubMethod and s.DirectSteps > ' ' end if @UsePositiveSteps = 1 begin insert into @FlowShell(UID_Element16 , UID_Element2 ) select distinct s.UID_PWODecisionStep , n.UID_PWODecisionStep from pwodecisionstep s join pwodecisionstep n on s.UID_PWODecisionSubMethod7 = @UID_PWODecisionSubMethod and n.UID_PWODecisionSubMethod = @UID_PWODecisionSubMethod and s.PositiveSteps <> 0  and s.UID_PWODecisionStep <> n.UID_PWODecisionStep8 and s.levelnumber + s.PositiveSteps = n.levelnumber end if @UseNegativeSteps = 1 begin insert into @FlowShell(UID_Element1 , UID_Element2 ) select distinct9 s.UID_PWODecisionStep , n.UID_PWODecisionStep from pwodecisionstep s join pwodecisionstep n on s.UID_PWODecisionSubMethod = @UID_PWODecisionSubMethod 10and n.UID_PWODecisionSubMethod = @UID_PWODecisionSubMethod and s.NegativeSteps <> 0  and s.UID_PWODecisionStep <> n.UID_PWODecisionStep and s.levelnumber11 + s.NegativeSteps = n.levelnumber where not exists (select top 1 1 from @FlowShell e where e.UID_Element1  = s.UID_PWODecisionStep and e.UID_Element2 12 = n.UID_PWODecisionStep ) end if @UseEscalationSteps = 1 begin insert into @FlowShell(UID_Element1 , UID_Element2 ) select distinct s.UID_PWODecisionStep13 , n.UID_PWODecisionStep from pwodecisionstep s join pwodecisionstep n on s.UID_PWODecisionSubMethod = @UID_PWODecisionSubMethod and n.UID_PWODecisionSubMethod14 = @UID_PWODecisionSubMethod and s.EscalationSteps <> 0  and s.UID_PWODecisionStep <> n.UID_PWODecisionStep and s.levelnumber + s.EscalationSteps = n.levelnumber15 where not exists (select top 1 1 from @FlowShell e where e.UID_Element1  = s.UID_PWODecisionStep and e.UID_Element2  = n.UID_PWODecisionStep ) end if 16@UseDirectSteps = 1 begin insert into @FlowShell(UID_Element1 , UID_Element2 ) select distinct s.UID_PWODecisionStep , n.UID_PWODecisionStep from pwodecisionstep17 s join @DirectHelp h on s.uid_pwodecisionstep = h.UID_SingleGuid  and s.UID_PWODecisionSubMethod = @UID_PWODecisionSubMethod and s.Directsteps > ' ' join18 pwodecisionstep n on n.UID_PWODecisionSubMethod = @UID_PWODecisionSubMethod  and s.UID_PWODecisionStep <> n.UID_PWODecisionStep and s.levelnumber + h.IntProperty19  = n.levelnumber where not exists (select top 1 1 from @FlowShell e where e.UID_Element1  = s.UID_PWODecisionStep and e.UID_Element2  = n.UID_PWODecisionStep20 ) end select @lauf = 1 while @lauf > 0 begin insert into @FlowShell(UID_Element1 , UID_Element2 ) select distinct s.UID_Element1  , n.UID_Element2  from21 @FlowShell s join @FlowShell n on s.UID_Element2  = n.UID_Element1  where not exists (select top 1 1 from @FlowShell e where e.UID_Element1  = s.UID_Element122  and e.UID_Element2  = n.UID_Element2  ) select @lauf = @@rowcount end if exists(select top 1 1 from @FlowShell where UID_Element1  = UID_Element2  ) 23begin select top 1 @erg = fs.UID_Element1  from @FlowShell fs where fs.UID_Element1  = fs.UID_Element2  end ende: return(@erg) end 24