Back to OIM Explorer

dbo.QBM_ZPreProptableFK

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Bulk DBQueue insert -> QBM-K-CommonPrepropTableFK / QBM_ZPreProptableFK at line 21; Bulk DBQueue insert -> QBM-K-CommonPrepropColumnFK / QBM_ZPrePropColumnFK at line 21; Bulk DBQueue insert -> QBM-K-CommonPrepropObject / QBM_ZPrePropObject at line 21; Bulk DBQueue insert -> QBM-K-CommonPrepropObject / QBM_ZPrePropObject at line 21

Source: sandbox-db sys.sql_modules

Source size: 5.421 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.
  • DBQueue relation detected. Follow the task procedure and referenced-by list for async processing.

Relations

  • Bulk DBQueue insert -> QBM-K-CommonPrepropTableFK / QBM_ZPreProptableFK at line 21
  • Bulk DBQueue insert -> QBM-K-CommonPrepropColumnFK / QBM_ZPrePropColumnFK at line 21
  • Bulk DBQueue insert -> QBM-K-CommonPrepropObject / QBM_ZPrePropObject at line 21
  • Bulk DBQueue insert -> QBM-K-CommonPrepropObject / QBM_ZPrePropObject at line 21
  • Bulk DBQueue insert -> QBM-K-CommonPrepropColumnFK / QBM_ZPrePropColumnFK at line 27
  • Bulk DBQueue insert -> QBM-K-CommonPrepropObject / QBM_ZPrePropObject at line 27
  • Bulk DBQueue insert -> QBM-K-CommonPrepropObject / QBM_ZPrePropObject at line 27
  • Bulk DBQueue insert -> QBM-K-CommonPrepropObject / QBM_ZPrePropObject at line 31
  • Bulk DBQueue insert -> QBM-K-CommonPrepropObject / QBM_ZPrePropObject at line 31
  • Bulk DBQueue insert -> QBM-K-CommonPrepropObject / QBM_ZPrePropObject at line 34
  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • queues DBQueue task QBM_ZPreProptableFK at line 21 Bulk DBQueue insert -> QBM-K-CommonPrepropTableFK / QBM_ZPreProptableFK at line 21
  • queues DBQueue task QBM_ZPrePropColumnFK at line 21 Bulk DBQueue insert -> QBM-K-CommonPrepropColumnFK / QBM_ZPrePropColumnFK at line 21
  • queues DBQueue task QBM_ZPrePropObject at line 21 Bulk DBQueue insert -> QBM-K-CommonPrepropObject / QBM_ZPrePropObject at line 21
  • queues DBQueue task QBM_ZPrePropColumnFK at line 27 Bulk DBQueue insert -> QBM-K-CommonPrepropColumnFK / QBM_ZPrePropColumnFK at line 27
  • queues DBQueue task QBM_ZPrePropObject at line 27 Bulk DBQueue insert -> QBM-K-CommonPrepropObject / QBM_ZPrePropObject at line 27
  • queues DBQueue task QBM_ZPrePropObject at line 31 Bulk DBQueue insert -> QBM-K-CommonPrepropObject / QBM_ZPrePropObject at line 31
  • queues DBQueue task QBM_ZPrePropObject at line 34 Bulk DBQueue insert -> QBM-K-CommonPrepropObject / QBM_ZPrePropObject at line 34
  • references source dbo.QBM_FGIPrepropConditionDeactiv source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_ZPrePropTable source text reference
  • queues DBQueue task QBM-K-CommonPrepropTableFK -> QBM_ZPreProptableFK QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropTableFK', @DBQueueElements_01 update DialogColumn set IsDeactivatedByPreProcessor = 1 , XDateUpdated = @Xdate , XUserUpdated = @XUser from DialogColumn c join DialogTable t on c.UID_DialogTable =…
  • queues DBQueue task QBM-K-CommonPrepropColumnFK -> QBM_ZPrePropColumnFK QBM_PDBQueueInsert_Bulk @UID_TaskFollow, @DBQueueElements_02 declare @DBQueueElements_03 QBM_YDBQueueRaw insert into @DBQueueElements_03 (object , subobject, genprocid) select x.uid, null, @GenProcID from (select distinct c.UID_DialogColum…
  • queues DBQueue task QBM-K-CommonPrepropObject -> QBM_ZPrePropObject QBM_PDBQueueInsert_Bulk @UID_TaskFollow, @DBQueueElements_03 declare @DBQueueElements_04 QBM_YDBQueueRaw insert into @DBQueueElements_04 (object , subobject, genprocid) select x.uid, null, @GenProcID from (select distinct o.UID_DialogObjec…

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL227 lines
1CREATE PROCEDURE QBM_ZPreProptableFK(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @GenProcID varchar(38)7  DECLARE @CountItems int8  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')9  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent10  DECLARE @XUser nvarchar(64) = object_name(@@procid)11  DECLARE @Xdate datetime = getutcdate()12  DECLARE @UID_TaskFollow varchar(38)13  BEGIN TRY14    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,15    UID_Parameter,16    UID_SubParameter,17    GenProcID)18    SELECT19      UID_DialogDBQueue,20      UID_Parameter,21      UID_SubParameter,22      GenProcID23    FROM QBMDBQueueCurrent cu24      WITH(readpast)25    WHERE26      cu.SlotNumber = @SlotNumber27    IF @@rowcount = 028    BEGIN29      GOTO EndLabel30    END31    DECLARE @PrePropHelper QBM_YPrepropHelper32    INSERT INTO @PrePropHelper(uid_parameter,33    IsDeactivated,34    GenProcID,35    PreProcessorCondition,36    IsDeactivatedOld)37    SELECT38      x.uid_parameter,39      0,40      x.GenProcID,41      isnull(t.PreProcessorCondition,42      N ''),43      t.isDeactivatedByPreProcessor44    FROM @DBQueueCurrent x45    JOIN dialogTable t46      ON x.uid_parameter = t.TableName47    UPDATE @PrePropHelper48    SET IsDeactivated = dbo.QBM_FGIPrepropConditionDeactiv(h.PreProcessorCondition)49    FROM @PrePropHelper h50    WHERE51      h.PreProcessorCondition > ' '52    UPDATE @PrePropHelper53    SET IsDeactivated = 154    FROM @PrePropHelper h55    JOIN(56    SELECT57      t.TableName,58      count(*) AS CountItems59    FROM dialogcolumn c60    JOIN DialogTable t61      ON c.UID_DialogTable = t.UID_DialogTable62    WHERE63      c.IsPKMember = 164    GROUP BY t.TableName) AS k165      ON k1.TableName = h.uid_parameter66    LEFT67    OUTER68    JOIN(69    SELECT70      t.TableName,71      count(*) AS CountItems72    FROM dialogcolumn c73    JOIN DialogTable t74      ON c.UID_DialogTable = t.UID_DialogTable75    WHERE76      c.IsPKMember = 1 AND isnull(c.IsDeactivatedByPreProcessor, 0) = 077    GROUP BY t.TableName) AS k278      ON k1.TableName = k2.TableName79    WHERE80      h.IsDeactivated = 0 AND isnull(k1.CountItems,81    0) <> isnull(k2.CountItems,82    0)83    UPDATE @PrePropHelper84    SET IsDeactivated = 185    FROM @PrePropHelper h86    JOIN dialogtable t87      ON h.uid_parameter = t.TableName88    JOIN dialogtable b89      ON t.UID_DialogTableBase = b.UID_DialogTable90    WHERE91      b.isDeactivatedByPreprocessor = 192    SELECT @GenProcID = max(GenProcID)93    FROM @DBQueueCurrent94    WHERE95      GenProcID > ' '96    EXEC QBM_PSessionContextSet 'GenProcID',97      @GenProcID98    UPDATE DialogTable99    SET IsDeactivatedByPreProcessor = x.isDeactivated,100    XDateUpdated = @Xdate,101    XUserUpdated = @XUser102    FROM DialogTable r103    JOIN @PrePropHelper x104      ON r.TableName = x.uid_parameter105    WHERE106      r.IsDeactivatedByPreProcessor <> x.IsDeactivated107    SELECT @CountItems = @@rowcount108    UPDATE DialogColumn109    SET IsDeactivatedByPreProcessor = 1,110    XDateUpdated = @Xdate,111    XUserUpdated = @XUser112    FROM DialogColumn c113    JOIN DialogTable t114      ON c.UID_DialogTable = t.UID_DialogTable115    JOIN @PrePropHelper x116      ON x.UID_Parameter = t.TableName117    WHERE118      x.IsDeactivated = 1 AND c.IsDeactivatedByPreProcessor = 0119    IF @CountItems > 0120    BEGIN121      DECLARE @DBQueueElements_01 QBM_YDBQueueRaw122      INSERT INTO @DBQueueElements_01(object,123      subobject,124      genprocid)125      SELECT126        x.uid,127        NULL,128        @GenProcID129      FROM(130      SELECT131        DISTINCT t.TableName AS UID,132        x.GenProcID133      FROM DialogTable t134      JOIN DialogTable b135        ON t.UID_DialogTableBase = b.UID_DialogTable136      JOIN @PrePropHelper x137        ON b.TableName = x.uid_parameter AND x.IsDeactivated <> x.IsDeactivatedOld) AS x138      EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropTableFK',139        @DBQueueElements_01140      UPDATE DialogColumn141      SET IsDeactivatedByPreProcessor = 1,142      XDateUpdated = @Xdate,143      XUserUpdated = @XUser144      FROM DialogColumn c145      JOIN DialogTable t146        ON c.UID_DialogTable = t.UID_DialogTable147      JOIN @PrePropHelper x148        ON x.uid_parameter = t.TableName149      WHERE150        x.IsDeactivatedOld = 0 AND x.IsDeactivated = 1 AND c.IsDeactivatedByPreProcessor = 0151      DECLARE @DBQueueElements_02 QBM_YDBQueueRaw152      INSERT INTO @DBQueueElements_02(object,153      subobject,154      genprocid)155      SELECT156        x.uid,157        NULL,158        @GenProcID159      FROM(160      SELECT161        DISTINCT c.UID_DialogColumn AS UID,162        x.GenProcID163      FROM DialogColumn c164      JOIN DialogTable t165        ON c.UID_DialogTable = t.UID_DialogTable166      JOIN @PrePropHelper x167        ON t.TableName = x.uid_parameter AND x.IsDeactivated = 0 AND x.IsDeactivatedOld = 1 AND c.isPKMember = 0) AS x168      SELECT @UID_TaskFollow = 'QBM-K-CommonPrepropColumn'169      EXEC QBM_PDBQueueInsert_Bulk @UID_TaskFollow,170        @DBQueueElements_02171      DECLARE @DBQueueElements_03 QBM_YDBQueueRaw172      INSERT INTO @DBQueueElements_03(object,173      subobject,174      genprocid)175      SELECT176        x.uid,177        NULL,178        @GenProcID179      FROM(180      SELECT181        DISTINCT c.UID_DialogColumn AS UID,182        x.GenProcID183      FROM DialogColumn c184      JOIN DialogTable t185        ON c.UID_DialogTable = t.UID_DialogTable186      JOIN @PrePropHelper x187        ON t.TableName = x.uid_parameter AND x.IsDeactivated <> x.IsDeactivatedOld AND c.ispkmember = 0 AND EXISTS(188      SELECT TOP 1 1189      FROM QBMRelation dcr190      WHERE191        dcr.UID_childcolumn = c.UID_DialogColumn)) AS x192      SELECT @UID_TaskFollow = 'QBM-K-CommonPrepropColumnFK'193      EXEC QBM_PDBQueueInsert_Bulk @UID_TaskFollow,194        @DBQueueElements_03195      DECLARE @DBQueueElements_04 QBM_YDBQueueRaw196      INSERT INTO @DBQueueElements_04(object,197      subobject,198      genprocid)199      SELECT200        x.uid,201        NULL,202        @GenProcID203      FROM(204      SELECT205        DISTINCT o.UID_DialogObject AS UID,206        x.GenProcID207      FROM DialogObject o208      JOIN DialogTable t209        ON o.UID_DialogTable = t.UID_DialogTable210      JOIN @PrePropHelper x211        ON t.TableName = x.uid_parameter AND x.IsDeactivated <>x.IsDeactivatedOld) AS x212      EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropObject',213        @DBQueueElements_04214    END215  END TRY216  BEGIN CATCH217    EXEC QBM_PSessionErrorAdd DEFAULT218    RAISERROR('',219    18,220    1)221      WITH NOWAIT222  END CATCH223  endLabel:224  EXEC QBM_PSessionContextSet 'GenProcID',225    @GenProcID_R226  RETURN227END
Open raw exported source
SQL · Raw36 lines
1      create   procedure QBM_ZPreProptableFK (@SlotNumber int) as begin  declare @GenProcID varchar(38) declare @CountItems int  declare @GenProcID_R2 varchar(38) = dbo.QBM_FGISessionContext('') declare @DBQueueCurrent QBM_YDBQueueCurrent declare @XUser nvarchar(64) = object_name(@@procid) declare @Xdate3 datetime = getutcdate() declare @UID_TaskFollow varchar(38) BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, 4GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber5 if @@rowcount = 0 begin goto EndLabel end declare @PrePropHelper QBM_YPrepropHelper insert into @PrePropHelper (uid_parameter , IsDeactivated , GenProcID6 , PreProcessorCondition, IsDeactivatedOld) select x.uid_parameter, 0, x.GenProcID, isnull(t.PreProcessorCondition,N''), t.isDeactivatedByPreProcessor 7from @DBQueueCurrent x join dialogTable t on x.uid_parameter = t.TableName update @PrePropHelper set IsDeactivated = dbo.QBM_FGIPrepropConditionDeactiv8(h.PreProcessorCondition) from @PrePropHelper h where h.PreProcessorCondition > ' '   update @PrePropHelper set IsDeactivated = 1  from @PrePropHelper 9h join (select t.TableName, count(*) as CountItems from dialogcolumn c join DialogTable t on c.UID_DialogTable = t.UID_DialogTable where c.IsPKMember =10 1 group by t.TableName ) as k1 on k1.TableName = h.uid_parameter left outer join (select t.TableName, count(*) as CountItems from dialogcolumn c join 11DialogTable t on c.UID_DialogTable = t.UID_DialogTable where c.IsPKMember = 1 and isnull(c.IsDeactivatedByPreProcessor,0) = 0 group by t.TableName ) as12 k2 on k1.TableName = k2.TableName where h.IsDeactivated = 0 and isnull(k1.CountItems,0) <> isnull(k2.CountItems,0)    update @PrePropHelper set IsDeactivated13 = 1 from @PrePropHelper h join dialogtable t on h.uid_parameter = t.TableName join dialogtable b on t.UID_DialogTableBase = b.UID_DialogTable where b.isDeactivatedByPreprocessor14 = 1    select @GenProcID = max(GenProcID) from @DBQueueCurrent where GenProcID > ' ' exec QBM_PSessionContextSet 'GenProcID', @GenProcID update DialogTable15 set IsDeactivatedByPreProcessor = x.isDeactivated , XDateUpdated = @Xdate , XUserUpdated = @XUser  from DialogTable r join @PrePropHelper x on r.TableName16 = x.uid_parameter where r.IsDeactivatedByPreProcessor <> x.IsDeactivated select @CountItems = @@rowcount     update DialogColumn set IsDeactivatedByPreProcessor17 = 1 , XDateUpdated = @Xdate , XUserUpdated = @XUser from DialogColumn c join DialogTable t on c.UID_DialogTable = t.UID_DialogTable join @PrePropHelper18 x on x.UID_Parameter = t.TableName where x.IsDeactivated = 1 and c.IsDeactivatedByPreProcessor = 0  if @CountItems > 0 begin    declare @DBQueueElements_0119 QBM_YDBQueueRaw insert into @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null, @GenProcID from (select distinct t.TableName as UID20, x.GenProcID from DialogTable t join DialogTable b on t.UID_DialogTableBase = b.UID_DialogTable join @PrePropHelper x on b.TableName = x.uid_parameter21 and x.IsDeactivated <> x.IsDeactivatedOld ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropTableFK', @DBQueueElements_01   update DialogColumn 22set IsDeactivatedByPreProcessor = 1 , XDateUpdated = @Xdate , XUserUpdated = @XUser from DialogColumn c join DialogTable t on c.UID_DialogTable = t.UID_DialogTable23 join @PrePropHelper x on x.uid_parameter = t.TableName where x.IsDeactivatedOld = 0 and x.IsDeactivated = 1 and c.IsDeactivatedByPreProcessor = 0 declare24 @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_02 (object, subobject, genprocid) select x.uid, null, @GenProcID from (select distinct25 c.UID_DialogColumn as UID, x.GenProcID from DialogColumn c join DialogTable t on c.UID_DialogTable = t.UID_DialogTable join @PrePropHelper x on t.TableName26 = x.uid_parameter   and x.IsDeactivated = 0 and x.IsDeactivatedOld = 1 and c.isPKMember = 0 ) as x  select @UID_TaskFollow = 'QBM-K-CommonPrepropColumn'27 exec QBM_PDBQueueInsert_Bulk @UID_TaskFollow, @DBQueueElements_02  declare @DBQueueElements_03 QBM_YDBQueueRaw insert into @DBQueueElements_03 (object28, subobject, genprocid) select x.uid, null, @GenProcID from (select distinct c.UID_DialogColumn as UID, x.GenProcID from DialogColumn c join DialogTable29 t on c.UID_DialogTable = t.UID_DialogTable join @PrePropHelper x on t.TableName = x.uid_parameter and x.IsDeactivated <> x.IsDeactivatedOld and c.ispkmember30 = 0 and exists (select top 1 1 from QBMRelation dcr where dcr.UID_childcolumn = c.UID_DialogColumn ) ) as x  select @UID_TaskFollow = 'QBM-K-CommonPrepropColumnFK'31 exec QBM_PDBQueueInsert_Bulk @UID_TaskFollow, @DBQueueElements_03  declare @DBQueueElements_04 QBM_YDBQueueRaw insert into @DBQueueElements_04 (object32, subobject, genprocid) select x.uid, null, @GenProcID from (select distinct o.UID_DialogObject as UID, x.GenProcID from DialogObject o join DialogTable33 t on o.UID_DialogTable = t.UID_DialogTable join @PrePropHelper x on t.TableName = x.uid_parameter and x.IsDeactivated <>x.IsDeactivatedOld ) as x exec34 QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropObject', @DBQueueElements_04   end   END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 3518, 1) WITH NOWAIT END CATCH  endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R return end 36