Back to OIM Explorer

dbo.QBM_PProcessGroupCreate_help

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 6.611 characters

Interpretation

Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

No extracted relations.

Typed Edges

references source dbo.QBM_PProcessGroupCreate source text reference

References

dbo.QBM_PProcessGroupCreate

Referenced By

dbo.QBM_PProcessGroupCreate

Complete Source

1CREATE PROCEDURE QBM_PProcessGroupCreate_help(2  @DebugSwitch int,3  @BlockSizeWork int,4  @GesamtAnzahl int OUTPUT,5  @XUser nvarchar(64),6  @Xdate datetime,7  @LimitTotal int8)9AS10BEGIN11  DECLARE @laufInnen int12  DECLARE @AnzahlInnerLoopCycles int = 013  DECLARE @BisherGefuellt int = 014  CREATE TABLE #sammler_0(15    GenProcID varchar(38) collate database_default NULL,16    GenProcIDGroup varchar(38) collate database_default NULL,17    index QBM_YProcessGroupCreate1(GenProcID,18    GenProcIDGroup),19    index QBM_YProcessGroupCreate2(GenProcIDGroup,20    GenProcID)21  )22  CREATE TABLE #sammler(23    GenProcID varchar(38) collate database_default NULL,24    GenProcIDGroup varchar(38) collate database_default NULL,25    index QBM_YProcessGroupCreate1(GenProcID,26    GenProcIDGroup),27    index QBM_YProcessGroupCreate2(GenProcIDGroup,28    GenProcID)29  ) zuruecksetzen:30  INSERT INTO #sammler_0(GenProcID,31  GenProcIDGroup32)33SELECT34  p.GenProcID,35  isnull(su.GenProcIDNew,36  p.GenProcID37  )38  AS39  GenProcIDGroup40FROM(41SELECT TOP(@BlockSizeWork) a.UID_SingleGuid AS GenProcID42FROM #DialogProcessMirror a43WHERE44  a.BitProperty = 0 AND a.IntProperty = 245ORDER BY a.XdateUpdated ASC) p46LEFT47OUTER48JOIN DialogProcessSubstitute su49  ON p.GenProcID = su.GenProcIDOrigin50SELECT @BisherGefuellt = @@ROWCOUNT51IF(@BisherGefuellt > @LimitTotal / 25 OR @BisherGefuellt > @BlockSizeWork * 3) AND @BlockSizeWork >= 1052BEGIN53  truncate TABLE #sammler_0 truncate TABLE #sammler54  SELECT @BlockSizeWork /= 1055  IF @DebugSwitch > 056  BEGIN57    print CONCAT('@BlockSizeWork (1) reduziert auf ',58    str(@BlockSizeWork))59  END60  GOTO zuruecksetzen61END62INSERT INTO #sammler_0(GenProcID,63GenProcIDGroup)64SELECT65  a.GenProcIDOrigin,66  a.GenProcIDNew67FROM(68SELECT69  su.GenProcIDOrigin,70  su.GenProcIDNew71FROM #sammler_0 s72JOIN DialogProcessSubstitute su73  ON s.GenProcIDGroup = su.GenProcIDNew74GROUP BY su.GenProcIDOrigin,75su.GenProcIDNew) AS a76LEFT77OUTER78JOIN #sammler_0 e79  ON e.GenProcID = a.GenProcIDOrigin AND e.GenProcIDGroup = a.GenProcIDNew80WHERE81  e.GenProcID IS NULL82SELECT @BisherGefuellt += @@rowcount83IF(@BisherGefuellt > @LimitTotal / 25 OR @BisherGefuellt > @BlockSizeWork * 4) AND @BlockSizeWork >= 1084BEGIN85  truncate TABLE #sammler_0 truncate TABLE #sammler86  SELECT @BlockSizeWork /= 1087  IF @DebugSwitch > 088  BEGIN89    print CONCAT('@BlockSizeWork (2) reduziert auf ',90    str(@BlockSizeWork))91  END92  GOTO zuruecksetzen93END94INSERT INTO #sammler(GenProcID,95GenProcIDGroup)96SELECT97  a.GenProcID,98  a.GenProcIDGroup99FROM(100SELECT101  s3.GenProcID AS GenProcID,102  v.GruppeAlt AS GenProcIDGroup103FROM(104SELECT105  s.GenProcIDGroup AS GruppeAlt, s2.GenProcIDGroup AS GruppeNeu106FROM #sammler_0 s107JOIN #sammler_0 s2108  ON s.GenProcID = s2.GenProcID AND s.GenProcIDGroup <> s2.GenProcIDGroup109GROUP BY s.GenProcIDGroup, s2.GenProcIDGroup) AS v110JOIN #sammler_0 s3111  ON v.GruppeNeu = s3.GenProcIDGroup112UNION113SELECT114  e.GenProcID,115  e.GenProcIDGroup116FROM #sammler_0 e) AS a117SELECT @BisherGefuellt = @@ROWCOUNT118IF(@BisherGefuellt > @LimitTotal / 25 OR @BisherGefuellt > @BlockSizeWork * 5) AND @BlockSizeWork >= 10119BEGIN120  truncate TABLE #sammler_0 truncate TABLE #sammler121  SELECT @BlockSizeWork /= 10122  IF @DebugSwitch > 0123  BEGIN124    print CONCAT('@BlockSizeWork (3) reduziert auf ',125    str(@BlockSizeWork))126  END127  GOTO zuruecksetzen128END129SELECT @laufInnen = 1130SELECT @AnzahlInnerLoopCycles = 0131WHILE @laufInnen > 0132BEGIN133  SELECT @laufInnen = 0134  INSERT INTO #sammler(GenProcID,135  GenProcIDGroup)136  SELECT137    a.GenProcID,138    a.GenProcIDGroup139  FROM(140  SELECT141    x.GenProcID,142    x.GenProcIDGroup143  FROM(144  SELECT145    su.GenProcIDOrigin AS GenProcID, s.GenProcIDGroup146  FROM #sammler s147  JOIN DialogProcessSubstitute su148    ON s.GenProcIDGroup = su.GenProcIDNew) AS x149  GROUP BY x.GenProcID,150  x.GenProcIDGroup) AS a151  LEFT152  OUTER153  JOIN #sammler e154    ON e.GenProcID = a.GenProcID155  WHERE156    e.GenProcID IS NULL157  SELECT @laufInnen += @@rowcount158  INSERT INTO #sammler(GenProcID,159  GenProcIDGroup)160  SELECT161    a.GenProcID,162    a.GenProcIDGroup163  FROM(164  SELECT165    x.GenProcID,166    x.GenProcIDGroup167  FROM(168  SELECT169    su.GenProcIDNew AS GenProcID, s.GenProcIDGroup170  FROM #sammler s171  JOIN DialogProcessSubstitute su172    ON s.GenProcIDGroup = su.GenProcIDOrigin) AS x173  GROUP BY x.GenProcID,174  x.GenProcIDGroup) AS a175  LEFT176  OUTER177  JOIN #sammler e178    ON e.GenProcID = a.GenProcID179  WHERE180    e.GenProcID IS NULL181  SELECT @laufInnen += @@rowcount182  INSERT INTO #sammler(GenProcID,183  GenProcIDGroup)184  SELECT185    a.GenProcID,186    a.GenProcIDGroup187  FROM(188  SELECT189    x.GenProcID,190    x.GenProcIDGroup191  FROM(192  SELECT193    su.GenProcIDNew AS GenProcID, s.GenProcIDGroup194  FROM #sammler s195  JOIN DialogProcessSubstitute su196    ON s.GenProcID = su.GenProcIDOrigin) AS x197  GROUP BY x.GenProcID,198  x.GenProcIDGroup) AS a199  LEFT200  OUTER201  JOIN #sammler e202    ON e.GenProcID = a.GenProcID203  WHERE204    e.GenProcID IS NULL205  SELECT @laufInnen += @@rowcount206  INSERT INTO #sammler(GenProcID,207  GenProcIDGroup)208  SELECT209    a.GenProcID,210    a.GenProcIDGroup211  FROM(212  SELECT213    x.GenProcID,214    x.GenProcIDGroup215  FROM(216  SELECT217    DISTINCT su.GenProcIDOrigin AS GenProcID, s.GenProcIDGroup218  FROM #sammler s219  JOIN DialogProcessSubstitute su220    ON s.GenProcID = su.GenProcIDNew) AS x221  GROUP BY x.GenProcID,222  x.GenProcIDGroup) AS a223  LEFT224  OUTER225  JOIN #sammler e226    ON e.GenProcID = a.GenProcID227  WHERE228    e.GenProcID IS NULL229  SELECT @laufInnen += @@rowcount230  SELECT @AnzahlInnerLoopCycles += 1231  IF @DebugSwitch > 0232  BEGIN233    print CONCAT('Anzahl in #sammler eingefügt',234    str(@laufInnen))235  END236END237IF @DebugSwitch > 0238BEGIN239  print CONCAT('Anzahl der Läufe für -- 4) Überbrückungen finden ... ',240  str(@AnzahlInnerLoopCycles)) print '#sammler ergänzt'241  IF @DebugSwitch = 2242  BEGIN243    SELECT *244    FROM #sammler245  END246  ELSE247  BEGIN248    SELECT COUNT(*)249    FROM #sammler250  END251END252UPDATE DialogProcess253SET GenProcIDGroup = f.GenProcIDGroup,254XDateUpdated = @Xdate,255XUserUpdated = @XUser256FROM DialogProcess p257JOIN(258SELECT259  x.GenProcID,260CASE261  WHEN y.ReadySum > 0 THEN262NULL263ELSE x.GenProcIDGroup264END AS GenProcIDGroup265FROM(266SELECT267  s.GenProcID, max(s.GenProcIDGroup) AS GenProcIDGroup268FROM #sammler s269GROUP BY s.GenProcID) AS x270JOIN(271SELECT272  s.GenProcIDGroup, sum(sign(p.ReadyForDeleteOrExport^2)) AS ReadySum273FROM #sammler s274JOIN DialogProcess p275  ON s.GenProcID = p.GenProcID276GROUP BY s.GenProcIDGroup) AS y277  ON x.GenProcIDGroup = y.GenProcIDGroup) AS f278  ON p.GenProcID = f.GenProcID279SELECT @gesamtanzahl += @@rowcount280UPDATE #DialogProcessMirror281SET BitProperty = 1282FROM #DialogProcessMirror m283JOIN #sammler s284  ON m.UID_SingleGuid = s.GenProcID285IF @DebugSwitch > 0286BEGIN287  print 'DialogProcess aktualisiert'288  IF @DebugSwitch = 2289  BEGIN290    SELECT291      ReadyForDeleteORExport,292      GenProcIDGroup,293      *294    FROM DialogProcess295  END296  ELSE297  BEGIN298    SELECT COUNT(*)299    FROM #DialogProcessMirror300    WHERE301      BitProperty = 1302  END303END304END

Open raw exported source

1     create   procedure QBM_PProcessGroupCreate_help( @DebugSwitch int , @BlockSizeWork int , @GesamtAnzahl int output , @XUser nvarchar(64) , @Xdate2 datetime , @LimitTotal int  ) as begin declare @laufInnen int declare @AnzahlInnerLoopCycles int = 0 declare @BisherGefuellt int = 0 create table #sammler_03 ( GenProcID varchar(38) collate database_default NULL , GenProcIDGroup varchar(38) collate database_default NULL , index QBM_YProcessGroupCreate1 (GenProcID4, GenProcIDGroup) , index QBM_YProcessGroupCreate2 (GenProcIDGroup, GenProcID) )  create table #sammler ( GenProcID varchar(38) collate database_default5 NULL , GenProcIDGroup varchar(38) collate database_default NULL , index QBM_YProcessGroupCreate1 (GenProcID, GenProcIDGroup) , index QBM_YProcessGroupCreate26 (GenProcIDGroup, GenProcID) )     zuruecksetzen:  insert into #sammler_0 (GenProcID, GenProcIDGroup) select p.GenProcID, isnull(su.GenProcIDNew, p.GenProcID7) as GenProcIDGroup from (        select top ( @BlockSizeWork) a.UID_SingleGuid as GenProcID from #DialogProcessMirror a where a.BitProperty = 0  and a.IntProperty8 = 2  order by a.XdateUpdated asc ) p left outer join DialogProcessSubstitute su on p.GenProcID = su.GenProcIDOrigin select @BisherGefuellt = @@ROWCOUNT9             if (@BisherGefuellt > @LimitTotal / 25 or @BisherGefuellt > @BlockSizeWork * 3) and @BlockSizeWork >= 10 begin truncate table #sammler_0 truncate10 table #sammler select @BlockSizeWork /= 10 if @DebugSwitch > 0 begin print concat('@BlockSizeWork (1) reduziert auf ', str(@BlockSizeWork)) end  goto 11zuruecksetzen end  insert into #sammler_0 (GenProcID, GenProcIDGroup) select a.GenProcIDOrigin, a.GenProcIDNew from ( select su.GenProcIDOrigin, su.GenProcIDNew12 from #sammler_0 s join DialogProcessSubstitute su on s.GenProcIDGroup = su.GenProcIDNew group by su.GenProcIDOrigin, su.GenProcIDNew ) as a left outer13 join #sammler_0 e on e.GenProcID = a.GenProcIDOrigin and e.GenProcIDGroup = a.GenProcIDNew where e.GenProcID is null select @BisherGefuellt += @@rowcount14             if (@BisherGefuellt > @LimitTotal / 25 or @BisherGefuellt > @BlockSizeWork * 4) and @BlockSizeWork >= 10 begin truncate table #sammler_0 truncate15 table #sammler select @BlockSizeWork /= 10 if @DebugSwitch > 0 begin print concat('@BlockSizeWork (2) reduziert auf ', str(@BlockSizeWork)) end  goto 16zuruecksetzen end  insert into #sammler (GenProcID, GenProcIDGroup) select a.GenProcID, a.GenProcIDGroup from ( select s3.GenProcID as GenProcID, v.GruppeAlt17 as GenProcIDGroup from (  select s.GenProcIDGroup as GruppeAlt, s2.GenProcIDGroup as GruppeNeu  from #sammler_0 s join #sammler_0 s2 on s.GenProcID = 18s2.GenProcID and s.GenProcIDGroup <> s2.GenProcIDGroup group by s.GenProcIDGroup, s2.GenProcIDGroup ) as v join #sammler_0 s3 on v.GruppeNeu  = s3.GenProcIDGroup19 union select e.GenProcID, e.GenProcIDGroup from #sammler_0 e ) as a  select @BisherGefuellt = @@ROWCOUNT             if (@BisherGefuellt > @LimitTotal20 / 25 or @BisherGefuellt > @BlockSizeWork * 5) and @BlockSizeWork >= 10 begin truncate table #sammler_0 truncate table #sammler select @BlockSizeWork /=21 10 if @DebugSwitch > 0 begin print concat('@BlockSizeWork (3) reduziert auf ', str(@BlockSizeWork)) end  goto zuruecksetzen end select @laufInnen = 1 22select @AnzahlInnerLoopCycles = 0  while @laufInnen > 0 begin select @laufInnen = 0  insert into #sammler (GenProcID, GenProcIDGroup) select a.GenProcID23, a.GenProcIDGroup from ( select x.GenProcID, x.GenProcIDGroup from (  select su.GenProcIDOrigin as GenProcID, s.GenProcIDGroup from #sammler s join DialogProcessSubstitute24 su on s.GenProcIDGroup = su.GenProcIDNew ) as x group by x.GenProcID, x.GenProcIDGroup ) as a left outer join #sammler e on e.GenProcID = a.GenProcID 25where e.GenProcID is null select @laufInnen += @@rowcount  insert into #sammler (GenProcID, GenProcIDGroup) select a.GenProcID, a.GenProcIDGroup from (26 select x.GenProcID, x.GenProcIDGroup from (  select su.GenProcIDNew as GenProcID, s.GenProcIDGroup from #sammler s join DialogProcessSubstitute su on 27s.GenProcIDGroup = su.GenProcIDOrigin ) as x group by x.GenProcID, x.GenProcIDGroup )as a left outer join #sammler e on e.GenProcID = a.GenProcID where28 e.GenProcID is null select @laufInnen += @@rowcount  insert into #sammler (GenProcID, GenProcIDGroup) select a.GenProcID, a.GenProcIDGroup from ( select29 x.GenProcID, x.GenProcIDGroup from (  select su.GenProcIDNew as GenProcID, s.GenProcIDGroup from #sammler s join DialogProcessSubstitute su on s.GenProcID30 = su.GenProcIDOrigin ) as x group by x.GenProcID, x.GenProcIDGroup )as a left outer join #sammler e on e.GenProcID = a.GenProcID where e.GenProcID is 31null select @laufInnen += @@rowcount  insert into #sammler (GenProcID, GenProcIDGroup) select a.GenProcID, a.GenProcIDGroup from ( select x.GenProcID, 32x.GenProcIDGroup from (  select distinct su.GenProcIDOrigin as GenProcID, s.GenProcIDGroup from #sammler s join DialogProcessSubstitute su on s.GenProcID33 = su.GenProcIDNew ) as x group by x.GenProcID, x.GenProcIDGroup )as a left outer join #sammler e on e.GenProcID = a.GenProcID where e.GenProcID is null34 select @laufInnen += @@rowcount select @AnzahlInnerLoopCycles += 1 if @DebugSwitch > 0 begin print concat('Anzahl in #sammler eingefügt', str(@laufInnen35) ) end end  if @DebugSwitch > 0 begin print concat('Anzahl der Läufe für -- 4) Überbrückungen finden ... ', str(@AnzahlInnerLoopCycles)) print '#sammler ergänzt'36 if @DebugSwitch = 2 begin select * from #sammler end else begin select COUNT(*) from #sammler end end     update DialogProcess set GenProcIDGroup = f.GenProcIDGroup37  , XDateUpdated = @Xdate , XUserUpdated = @XUser  from DialogProcess p join( select x.GenProcID , case when y.ReadySum > 0 then null else x.GenProcIDGroup38 end as GenProcIDGroup from( select s.GenProcID , max(s.GenProcIDGroup) as GenProcIDGroup from #sammler s group by s.GenProcID ) as x join( select s.GenProcIDGroup39 , sum(sign(p.ReadyForDeleteOrExport^2)) as ReadySum from #sammler s join DialogProcess p on s.GenProcID = p.GenProcID group by s.GenProcIDGroup ) as y40 on x.GenProcIDGroup = y.GenProcIDGroup ) as f on p.GenProcID = f.GenProcID select @gesamtanzahl += @@rowcount update #DialogProcessMirror set BitProperty41 = 1 from #DialogProcessMirror m join #sammler s on m.UID_SingleGuid = s.GenProcID if @DebugSwitch > 0 begin print 'DialogProcess aktualisiert' if @DebugSwitch42 = 2 begin select ReadyForDeleteORExport,GenProcIDGroup,* from DialogProcess end else begin select COUNT(*) from #DialogProcessMirror where BitProperty43 = 1 end end  end 44