Back to OIM Explorer

dbo.QBM_ZPrePropColumn

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Bulk DBQueue insert -> QBM-K-CommonPrepropColumn / QBM_ZPrePropColumn at line 17; Bulk DBQueue insert -> QBM-K-CommonPrepropColumnFK / QBM_ZPrePropColumnFK at line 17; Bulk DBQueue insert -> QBM-K-CommonPrepropTableFK / QBM_ZPreProptableFK at line 17; Bulk DBQueue insert -> QBM-K-CommonRebuildView / QBM_ZViewBuildAll at line 17

Source: sandbox-db sys.sql_modules

Source size: 5.089 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-CommonPrepropColumn / QBM_ZPrePropColumn at line 17
  • Bulk DBQueue insert -> QBM-K-CommonPrepropColumnFK / QBM_ZPrePropColumnFK at line 17
  • Bulk DBQueue insert -> QBM-K-CommonPrepropTableFK / QBM_ZPreProptableFK at line 17
  • Bulk DBQueue insert -> QBM-K-CommonRebuildView / QBM_ZViewBuildAll at line 17
  • Bulk DBQueue insert -> QBM-K-CommonPrepropColumnFK / QBM_ZPrePropColumnFK at line 22
  • Bulk DBQueue insert -> QBM-K-CommonPrepropTableFK / QBM_ZPreProptableFK at line 22
  • Bulk DBQueue insert -> QBM-K-CommonRebuildView / QBM_ZViewBuildAll at line 22
  • Bulk DBQueue insert -> QBM-K-CommonPrepropTableFK / QBM_ZPreProptableFK at line 25
  • Bulk DBQueue insert -> QBM-K-CommonRebuildView / QBM_ZViewBuildAll at line 25
  • Bulk DBQueue insert -> QBM-K-CommonRebuildView / QBM_ZViewBuildAll at line 31
  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • queues DBQueue task QBM_ZPrePropColumn at line 17 Bulk DBQueue insert -> QBM-K-CommonPrepropColumn / QBM_ZPrePropColumn at line 17
  • queues DBQueue task QBM_ZPrePropColumnFK at line 17 Bulk DBQueue insert -> QBM-K-CommonPrepropColumnFK / QBM_ZPrePropColumnFK at line 17
  • queues DBQueue task QBM_ZPreProptableFK at line 17 Bulk DBQueue insert -> QBM-K-CommonPrepropTableFK / QBM_ZPreProptableFK at line 17
  • queues DBQueue task QBM_ZViewBuildAll at line 17 Bulk DBQueue insert -> QBM-K-CommonRebuildView / QBM_ZViewBuildAll at line 17
  • queues DBQueue task QBM_ZPrePropColumnFK at line 22 Bulk DBQueue insert -> QBM-K-CommonPrepropColumnFK / QBM_ZPrePropColumnFK at line 22
  • queues DBQueue task QBM_ZPreProptableFK at line 22 Bulk DBQueue insert -> QBM-K-CommonPrepropTableFK / QBM_ZPreProptableFK at line 22
  • queues DBQueue task QBM_ZViewBuildAll at line 22 Bulk DBQueue insert -> QBM-K-CommonRebuildView / QBM_ZViewBuildAll at line 22
  • queues DBQueue task QBM_ZPreProptableFK at line 25 Bulk DBQueue insert -> QBM-K-CommonPrepropTableFK / QBM_ZPreProptableFK at line 25
  • queues DBQueue task QBM_ZViewBuildAll at line 25 Bulk DBQueue insert -> QBM-K-CommonRebuildView / QBM_ZViewBuildAll at line 25
  • queues DBQueue task QBM_ZViewBuildAll at line 31 Bulk DBQueue insert -> QBM-K-CommonRebuildView / QBM_ZViewBuildAll at line 31
  • 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_PDBQueueInsert_WaitForComp source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • queues DBQueue task QBM-K-CommonPrepropColumn -> QBM_ZPrePropColumn QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropColumn', @DBQueueElements_01 declare @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_02 (object, subobject, genprocid) select x.UID, null, @GenProcID from (select UID_DialogColum…
  • queues DBQueue task QBM-K-CommonPrepropColumnFK -> QBM_ZPrePropColumnFK QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropColumnFK', @DBQueueElements_02 declare @DBQueueElements_03 QBM_YDBQueueRaw insert into @DBQueueElements_03 (object, subobject, genprocid) select x.uid, null, @GenProcID from (select distinct t.Ta…
  • queues DBQueue task QBM-K-CommonPrepropTableFK -> QBM_ZPreProptableFK QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropTableFK' , @DBQueueElements_03 if @CountItems > 0 begin declare @DBQueueElements_04 QBM_YDBQueueRaw insert into @DBQueueElements_04 (object, subobject, genprocid ) select x.uid, null, @GenProcID …
  • queues DBQueue task QBM-K-CommonRebuildView -> QBM_ZViewBuildAll QBM_PDBQueueInsert_Bulk 'QBM-K-CommonRebuildView', @DBQueueElements_04 end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R re…

References

Referenced By

Complete Source

SQL209 lines
1CREATE PROCEDURE QBM_ZPrePropColumn(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  BEGIN TRY11    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,12    UID_Parameter,13    UID_SubParameter,14    GenProcID)15    SELECT16      UID_DialogDBQueue,17      UID_Parameter,18      UID_SubParameter,19      GenProcID20    FROM QBMDBQueueCurrent cu21      WITH(readpast)22    WHERE23      cu.SlotNumber = @SlotNumber24    IF @@rowcount = 025    BEGIN26      GOTO EndLabel27    END28    SELECT @GenProcID = max(GenProcID)29    FROM @DBQueueCurrent30    WHERE31      GenProcID > ' '32    IF isnull(@Genprocid,33    '') = ''34    BEGIN35      SELECT @GenProcID = newid()36    END37    DECLARE @PrePropHelper QBM_YPrepropHelper38    INSERT INTO @PrePropHelper(uid_parameter,39    isDeactivatedPrior,40    IsDeactivated,41    GenProcID,42    PreProcessorCondition,43    IsDeactivatedOld)44    SELECT45      x.uid_parameter,46      isnull(t.isDeactivatedByPreProcessor,47      0),48      isnull(t.isDeactivatedByPreProcessor,49      0),50      x.GenProcID,51      isnull(c.PreProcessorCondition,52      N ''),53      c.isDeactivatedByPreProcessor54    FROM @DBQueueCurrent x55    JOIN dialogcolumn c56      ON x.uid_parameter = c.uid_dialogcolumn57    JOIN dialogtable t58      ON c.UID_DialogTable = t.UID_DialogTable59    UPDATE @PrePropHelper60    SET IsDeactivated = dbo.QBM_FGIPrepropConditionDeactiv(h.PreProcessorCondition)61    FROM @PrePropHelper h62    WHERE63      h.PreProcessorCondition > ' ' AND h.IsDeactivated = 064    UPDATE @PrePropHelper65    SET IsDeactivated = 166    FROM @PrePropHelper h67    JOIN QBM_VQBMRelation r68      ON r.UID_ChildColumn = h.uid_parameter69    JOIN DialogTable t70      ON r.UID_DialogTableParent= t.UID_DialogTable71    WHERE72      t.IsDeactivatedByPreProcessor = 173    IF EXISTS(74      SELECT TOP 1 175      FROM dialogcolumn c76      JOIN @PrePropHelper x77        ON c.UID_DialogColumn = x.uid_parameter78      WHERE79        c.IsDeactivatedByPreProcessor <> x.IsDeactivated AND NOT EXISTS(80      SELECT TOP 1 181      FROM QBMRelation r82      WHERE83        c.UID_DialogColumn = r.UID_ChildColumn))84    BEGIN85      EXEC QBM_PDBQueueInsert_WaitForComp 'Column',86        @GenProcID87    END88    EXEC QBM_PSessionContextSet 'GenProcID',89      @GenProcID90    UPDATE DialogColumn91    SET IsDeactivatedByPreProcessor = x.isDeactivated,92    xdateupdated = GetUTCDate(),93    xuserupdated = 'DBScheduler'94    FROM DialogColumn r95    JOIN @PrePropHelper x96      ON r.UID_DialogColumn = x.uid_parameter97    WHERE98      r.IsDeactivatedByPreProcessor <> x.IsDeactivated99    SELECT @CountItems = @@rowcount100    DECLARE @DBQueueElements_01 QBM_YDBQueueRaw101    INSERT INTO @DBQueueElements_01(object,102    subobject,103    genprocid)104    SELECT105      v.UID_DialogColumn,106      NULL,107      @GenProcID108    FROM dialogcolumn v109      WITH(nolock)110    JOIN @PrePropHelper x111      ON v.UID_BaseColumn = x.uid_parameter112    EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropColumn',113      @DBQueueElements_01114    DECLARE @DBQueueElements_02 QBM_YDBQueueRaw115    INSERT INTO @DBQueueElements_02(object,116    subobject,117    genprocid)118    SELECT119      x.UID,120      NULL,121      @GenProcID122    FROM(123    SELECT UID_DialogColumn AS UID124    FROM DialogColumn c125    JOIN @PrePropHelper x126      ON c.UID_baseColumn = x.uid_parameter AND(x.IsDeactivated <> x.IsDeactivatedOld)127    UNION128    SELECT UID_ChildColumn AS UID129    FROM QBMRelation cr130    JOIN @PrePropHelper x131      ON cr.UID_parentColumn = x.uid_parameter132    JOIN dialogcolumn c133      WITH(nolock)134      ON x.uid_parameter = c.uid_dialogcolumn AND c.isPKMember = 1135    UNION136    SELECT UID_ChildColumn AS UID137    FROM QBMRelation cr138    JOIN @PrePropHelper x139      ON cr.UID_ChildColumn = x.uid_parameter AND(x.IsDeactivated <> x.IsDeactivatedOld OR x.IsDeactivated = 0)) AS x140    EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropColumnFK',141      @DBQueueElements_02142    DECLARE @DBQueueElements_03 QBM_YDBQueueRaw143    INSERT INTO @DBQueueElements_03(object,144    subobject,145    genprocid)146    SELECT147      x.uid,148      NULL,149      @GenProcID150    FROM(151    SELECT152      DISTINCT t.TableName AS UID153    FROM @PrePropHelper x154    JOIN DialogColumn c155      WITH(nolock)156      ON x.uid_parameter = c.uid_dialogColumn157    JOIN DialogTable t158      WITH(nolock)159      ON c.UID_DialogTable = t.UID_DialogTable AND(x.IsDeactivated <> x.IsDeactivatedOld OR x.IsDeactivated = 1) AND c.isPKMember = 1) AS x160    EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropTableFK',161      @DBQueueElements_03162    IF @CountItems > 0163    BEGIN164      DECLARE @DBQueueElements_04 QBM_YDBQueueRaw165      INSERT INTO @DBQueueElements_04(object,166      subobject,167      genprocid)168      SELECT169        x.uid,170        NULL,171        @GenProcID172      FROM(173      SELECT tu.UID_DialogTable AS UID174      FROM @PrePropHelper x175      JOIN DialogColumn c176        ON x.uid_parameter = c.uid_dialogColumn177      JOIN DialogColumn u178        WITH(nolock)179        ON c.UID_DialogColumnUnionView = u.uid_dialogColumn180      JOIN DialogTable tu181        WITH(nolock)182        ON u.UID_DialogTable = tu.UID_DialogTable AND(x.IsDeactivated <> x.IsDeactivatedOld)183      UNION184      SELECT tv.UID_DialogTable AS UID185      FROM @PrePropHelper x186      JOIN DialogColumn c187        ON x.uid_parameter = c.uid_dialogColumn188      JOIN DialogColumn v189        WITH(nolock)190        ON v.UID_BaseColumn = c.uid_dialogColumn191      JOIN DialogTable tv192        WITH(nolock)193        ON v.UID_DialogTable = tv.UID_DialogTable AND(x.IsDeactivated <> x.IsDeactivatedOld)) AS x194      EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonRebuildView',195        @DBQueueElements_04196    END197  END TRY198  BEGIN CATCH199    EXEC QBM_PSessionErrorAdd DEFAULT200    RAISERROR('',201    18,202    1)203      WITH NOWAIT204  END CATCH205  endLabel:206  EXEC QBM_PSessionContextSet 'GenProcID',207    @GenProcID_R208  RETURN209END
Open raw exported source
SQL · Raw34 lines
1    create   procedure QBM_ZPrePropColumn (@SlotNumber int) as begin  declare @GenProcID varchar(38) declare @CountItems int  declare @GenProcID_R2 varchar(38) = dbo.QBM_FGISessionContext('') declare @DBQueueCurrent QBM_YDBQueueCurrent BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter3, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber4 = @SlotNumber if @@rowcount = 0 begin goto EndLabel end select @GenProcID = max(GenProcID) from @DBQueueCurrent where GenProcID > ' ' if isnull(@Genprocid5, '') = '' begin select @GenProcID = newid() end declare @PrePropHelper QBM_YPrepropHelper insert into @PrePropHelper (uid_parameter , isDeactivatedPrior6 , IsDeactivated , GenProcID , PreProcessorCondition, IsDeactivatedOld) select x.uid_parameter, isnull(t.isDeactivatedByPreProcessor,0), isnull(t.isDeactivatedByPreProcessor7,0), x.GenProcID, isnull(c.PreProcessorCondition,N''), c.isDeactivatedByPreProcessor from @DBQueueCurrent x join dialogcolumn c on x.uid_parameter = c.uid_dialogcolumn8 join dialogtable t on c.UID_DialogTable = t.UID_DialogTable   update @PrePropHelper set IsDeactivated = dbo.QBM_FGIPrepropConditionDeactiv(h.PreProcessorCondition9) from @PrePropHelper h where h.PreProcessorCondition > ' ' and h.IsDeactivated = 0  update @PrePropHelper set IsDeactivated = 1 from @PrePropHelper h 10join QBM_VQBMRelation r on r.UID_ChildColumn = h.uid_parameter join DialogTable t on r.UID_DialogTableParent= t.UID_DialogTable where t.IsDeactivatedByPreProcessor11 = 1 if exists (select top 1 1 from dialogcolumn c join @PrePropHelper x on c.UID_DialogColumn = x.uid_parameter where c.IsDeactivatedByPreProcessor <>12 x.IsDeactivated  and not exists (select top 1 1 from QBMRelation r where c.UID_DialogColumn = r.UID_ChildColumn ) ) begin  exec QBM_PDBQueueInsert_WaitForComp13 'Column', @GenProcID  end   exec QBM_PSessionContextSet 'GenProcID', @GenProcID update DialogColumn set IsDeactivatedByPreProcessor = x.isDeactivated,14 xdateupdated = GetUTCDate(), xuserupdated = 'DBScheduler'  from DialogColumn r join @PrePropHelper x on r.UID_DialogColumn = x.uid_parameter where r.IsDeactivatedByPreProcessor15 <> x.IsDeactivated select @CountItems = @@rowcount    declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_01 (object, subobject, 16genprocid) select v.UID_DialogColumn, null, @GenProcID from dialogcolumn v with (nolock) join @PrePropHelper x on v.UID_BaseColumn = x.uid_parameter   17exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropColumn', @DBQueueElements_01    declare @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_0218 (object, subobject, genprocid) select x.UID, null, @GenProcID from (select UID_DialogColumn as UID from DialogColumn c join @PrePropHelper x on c.UID_baseColumn19 = x.uid_parameter and (x.IsDeactivated <> x.IsDeactivatedOld  ) union select UID_ChildColumn as UID from QBMRelation cr join @PrePropHelper x on cr.UID_parentColumn20 = x.uid_parameter  join dialogcolumn c with (nolock) on x.uid_parameter = c.uid_dialogcolumn and c.isPKMember = 1 union   select UID_ChildColumn as UID21 from QBMRelation cr join @PrePropHelper x on cr.UID_ChildColumn = x.uid_parameter and (x.IsDeactivated <> x.IsDeactivatedOld or x.IsDeactivated = 0 ) 22) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropColumnFK', @DBQueueElements_02                   declare @DBQueueElements_03 QBM_YDBQueueRaw insert23 into @DBQueueElements_03 (object, subobject, genprocid) select x.uid, null, @GenProcID from (select distinct t.TableName as UID from @PrePropHelper x 24join DialogColumn c with (nolock) on x.uid_parameter = c.uid_dialogColumn join DialogTable t with (nolock) on c.UID_DialogTable = t.UID_DialogTable and25 ( x.IsDeactivated <> x.IsDeactivatedOld  or x.IsDeactivated = 1 ) and c.isPKMember = 1 ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropTableFK'26, @DBQueueElements_03 if @CountItems > 0 begin   declare @DBQueueElements_04 QBM_YDBQueueRaw insert into @DBQueueElements_04 (object, subobject, genprocid27) select x.uid, null, @GenProcID from (select tu.UID_DialogTable as UID from @PrePropHelper x join DialogColumn c on x.uid_parameter = c.uid_dialogColumn28 join DialogColumn u with (nolock) on c.UID_DialogColumnUnionView = u.uid_dialogColumn join DialogTable tu with (nolock) on u.UID_DialogTable = tu.UID_DialogTable29 and ( x.IsDeactivated <> x.IsDeactivatedOld ) union select tv.UID_DialogTable as UID from @PrePropHelper x join DialogColumn c on x.uid_parameter = c.uid_dialogColumn30 join DialogColumn v with (nolock) on v.UID_BaseColumn = c.uid_dialogColumn join DialogTable tv with (nolock) on v.UID_DialogTable = tv.UID_DialogTable31 and ( x.IsDeactivated <> x.IsDeactivatedOld ) ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonRebuildView', @DBQueueElements_04  end   END TRY BEGIN 32CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH  endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R return33 end 34