Back to OIM Explorer

dbo.QER_RITShopCheckDecision

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.119 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_FGIDBQueueSlotResetType source text reference
references source dbo.QBM_PDBQCS_CurrentMoveSlot source text reference
references source dbo.QBM_PSessionErrorAdd source text reference
references source dbo.QBM_PSlotResetWhileJobRun_L source text reference

References

Referenced By

No direct source references extracted.

Complete Source

1CREATE PROCEDURE QER_RITShopCheckDecision(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @ObjectKeysToCheck_Projection QBM_YParameterList7  DECLARE @ObjectKeysToCheck_Jobrunning QBM_YParameterList8  DECLARE @MyName nvarchar(64) = object_name(@@procid)9  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent10  DECLARE @resetted int11  DECLARE @DBQueueToMove QBM_YDBQCSCurrentToMove12  DECLARE @SlotNumberSource int13  DECLARE @SlotNumberTarget int14  DECLARE @RowsMoved int15  BEGIN TRY16    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,17    UID_Parameter,18    UID_SubParameter,19    GenProcID)20    SELECT21      UID_DialogDBQueue,22      UID_Parameter,23      UID_SubParameter,24      GenProcID25    FROM QBMDBQueueCurrent cu26      WITH(readpast)27    WHERE28      cu.SlotNumber = @SlotNumber29    IF @@rowcount = 030    BEGIN31      GOTO EndLabel32    END33    IF EXISTS(34      SELECT TOP 1 135      FROM @DBQueueCurrent cu36      JOIN PersonWantsOrg pwo37      WITH(readpast)38      ON pwo.UID_PersonWantsOrg = cu.UID_Parameter39    WHERE40      pwo.OrderState IN('Aborted', 'Assigned', 'Dismissed', 'Granted', 'New', 'Unsubscribed', 'Waiting'))41    BEGIN42      DELETE @DBQueueToMove43      SELECT @SlotNumberSource = @SlotNumber44      SELECT @SlotnumberTarget = 045      INSERT INTO @DBQueueToMove(UID_DialogDBQueue,46      AdditionalValue)47      SELECT48        cul.UID_DialogDBQueue,49        pwo.UID_PersonWantsOrg50      FROM @DBQueueCurrent cul51      JOIN PersonWantsOrg pwo52        ON pwo.UID_PersonWantsOrg = cul.UID_Parameter53      JOIN PWOHelperPWO h54        ON h.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg55      WHERE56        pwo.OrderState IN('Aborted',57      'Assigned',58      'Dismissed',59      'Granted',60      'New',61      'Unsubscribed',62      'Waiting')63      IF @@ROWCOUNT > 064      BEGIN65        DELETE PWOHelperPWO66        FROM PWOHelperPWO h67        JOIN @DBQueueToMove mo68          ON h.UID_PersonWantsOrg = mo.AdditionalValue69        EXEC @RowsMoved = QBM_PDBQCS_CurrentMoveSlot @DBQueueToMove,70          @SlotNumberSource,71          @SlotnumberTarget72        DELETE @DBQueueCurrent73        FROM @DBQueueCurrent cu74        JOIN @DBQueueToMove mo75          ON cu.UID_DialogDBQueue = mo.UID_DialogDBQueue76      END77    END78    INSERT INTO @ObjectKeysToCheck_Jobrunning(Parameter1,79    Parameter2)80    SELECT81      x.UID_PersonWantsOrg,82      x.XObjectKey83    FROM @DBQueueCurrent cu84    JOIN PersonwantsOrg x85      ON cu.UID_Parameter = x.UID_PersonWantsOrg86    UNION all87    SELECT88      cu.UID_Parameter,89      p.XObjectKey90    FROM @DBQueueCurrent cu91    JOIN PersonwantsOrg x92      ON cu.UID_Parameter = x.UID_PersonWantsOrg93    JOIN Person p94      ON x.UID_PersonOrdered = p.UID_Person95    EXEC @resetted = QBM_PSlotResetWhileJobRun_L @DBQueueCurrent,96      @@PROCID,97      @ObjectKeysToCheck_Jobrunning,98      @SlotNumber = @SlotNumber99    IF @resetted > 0100    BEGIN101      DELETE @DBQueueCurrent102      INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,103      UID_Parameter,104      UID_SubParameter,105      GenProcID)106      SELECT107        UID_DialogDBQueue,108        UID_Parameter,109        UID_SubParameter,110        GenProcID111      FROM QBMDBQueueCurrent cu112        WITH(readpast)113      WHERE114        cu.SlotNumber = @SlotNumber115      IF @@rowcount = 0116      BEGIN117        GOTO EndLabel118      END119    END120    SELECT @SlotNumberSource = @SlotNumber121    SELECT @SlotnumberTarget = dbo.QBM_FGIDBQueueSlotResetType('DBQueue')122    DELETE @DBQueueToMove123    INSERT INTO @DBQueueToMove(UID_DialogDBQueue)124    SELECT cul.UID_DialogDBQueue125    FROM @DBQueueCurrent cul126    WHERE127      EXISTS(128    SELECT TOP 1 1129    FROM DialogDBQueue q130      WITH(readpast)131    WHERE132      q.Object = cul.UID_Parameter AND q.Generation >= 0 AND q.UID_Task IN('QER-K-ShoppingRackPWOHelperPWO',133    'QER-K-ShoppingRackPWOHelperPWO-Cfg'))134    EXEC @RowsMoved = QBM_PDBQCS_CurrentMoveSlot @DBQueueToMove,135      @SlotNumberSource,136      @SlotnumberTarget137    DELETE @DBQueueCurrent138    FROM @DBQueueCurrent cu139    JOIN @DBQueueToMove mo140      ON cu.UID_DialogDBQueue = mo.UID_DialogDBQueue141    SELECT @resetted = @RowsMoved142    DELETE @DBQueueToMove143    INSERT INTO @DBQueueToMove(UID_DialogDBQueue)144    SELECT cul.UID_DialogDBQueue145    FROM @DBQueueCurrent cul146    WHERE147      EXISTS(148    SELECT TOP 1 1149    FROM QBMDBQueueCurrent q150      WITH(readpast)151    WHERE152      q.UID_Parameter = cul.UID_Parameter AND q.SlotNumber <> 0 AND q.UID_Task IN('QER-K-ShoppingRackPWOHelperPWO',153    'QER-K-ShoppingRackPWOHelperPWO-Cfg'))154    EXEC @RowsMoved = QBM_PDBQCS_CurrentMoveSlot @DBQueueToMove,155      @SlotNumberSource,156      @SlotnumberTarget157    DELETE @DBQueueCurrent158    FROM @DBQueueCurrent cu159    JOIN @DBQueueToMove mo160      ON cu.UID_DialogDBQueue = mo.UID_DialogDBQueue161    SELECT @resetted += @RowsMoved162  END TRY163  BEGIN CATCH164    EXEC QBM_PSessionErrorAdd DEFAULT165    RAISERROR('',166    18,167    1)168      WITH NOWAIT169  END CATCH170  endLabel:171END

Open raw exported source

1create   procedure QER_RITShopCheckDecision (@SlotNumber int) AS begin declare @ObjectKeysToCheck_Projection QBM_YParameterList declare @ObjectKeysToCheck_Jobrunning2 QBM_YParameterList declare @MyName nvarchar(64) = object_name(@@procid) declare @DBQueueCurrent QBM_YDBQueueCurrent declare @resetted int declare @DBQueueToMove3 QBM_YDBQCSCurrentToMove declare @SlotNumberSource int declare @SlotNumberTarget int declare @RowsMoved int BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue4, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast5) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end if exists (select top 1 1 from @DBQueueCurrent cu join PersonWantsOrg pwo6 with (readpast) on pwo.UID_PersonWantsOrg = cu.UID_Parameter   where pwo.OrderState in ('Aborted', 'Assigned', 'Dismissed', 'Granted', 'New', 'Unsubscribed'7, 'Waiting') ) begin delete @DBQueueToMove select @SlotNumberSource = @SlotNumber  select @SlotnumberTarget = 0  insert into @DBQueueToMove(UID_DialogDBQueue8, AdditionalValue) select cul.UID_DialogDBQueue, pwo.UID_PersonWantsOrg from @DBQueueCurrent cul join PersonWantsOrg pwo on pwo.UID_PersonWantsOrg = cul.UID_Parameter9 join PWOHelperPWO h on h.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg   where pwo.OrderState in ('Aborted', 'Assigned', 'Dismissed', 'Granted', 'New', 10'Unsubscribed', 'Waiting') if @@ROWCOUNT > 0 begin delete PWOHelperPWO from PWOHelperPWO h join @DBQueueToMove mo on h.UID_PersonWantsOrg = mo.AdditionalValue11 exec @RowsMoved = QBM_PDBQCS_CurrentMoveSlot @DBQueueToMove, @SlotNumberSource, @SlotnumberTarget  delete @DBQueueCurrent from @DBQueueCurrent cu join12 @DBQueueToMove mo on cu.UID_DialogDBQueue = mo.UID_DialogDBQueue end end   insert into @ObjectKeysToCheck_Jobrunning(Parameter1, Parameter2) select x.UID_PersonWantsOrg13, x.XObjectKey from @DBQueueCurrent cu join PersonwantsOrg x on cu.UID_Parameter = x.UID_PersonWantsOrg union all select cu.UID_Parameter, p.XObjectKey14 from @DBQueueCurrent cu join PersonwantsOrg x on cu.UID_Parameter = x.UID_PersonWantsOrg join Person p on x.UID_PersonOrdered = p.UID_Person  exec @resetted15 = QBM_PSlotResetWhileJobRun_L @DBQueueCurrent, @@PROCID, @ObjectKeysToCheck_Jobrunning, @SlotNumber = @SlotNumber if @resetted > 0 begin delete @DBQueueCurrent16 insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, 17GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end end  select @SlotNumberSource18 = @SlotNumber  select @SlotnumberTarget = dbo.QBM_FGIDBQueueSlotResetType('DBQueue') delete @DBQueueToMove  insert into @DBQueueToMove(UID_DialogDBQueue19) select cul.UID_DialogDBQueue from @DBQueueCurrent cul where exists (select top 1 1 from DialogDBQueue q with (readpast) where q.Object = cul.UID_Parameter20  and q.Generation >= 0 and q.UID_Task in ('QER-K-ShoppingRackPWOHelperPWO' , 'QER-K-ShoppingRackPWOHelperPWO-Cfg' ) ) exec @RowsMoved = QBM_PDBQCS_CurrentMoveSlot21 @DBQueueToMove, @SlotNumberSource, @SlotnumberTarget  delete @DBQueueCurrent from @DBQueueCurrent cu join @DBQueueToMove mo on cu.UID_DialogDBQueue = 22mo.UID_DialogDBQueue select @resetted = @RowsMoved delete @DBQueueToMove  insert into @DBQueueToMove(UID_DialogDBQueue) select cul.UID_DialogDBQueue from23 @DBQueueCurrent cul where exists (select top 1 1 from QBMDBQueueCurrent q with (readpast) where q.UID_Parameter = cul.UID_Parameter  and q.SlotNumber 24<> 0 and q.UID_Task in ('QER-K-ShoppingRackPWOHelperPWO' , 'QER-K-ShoppingRackPWOHelperPWO-Cfg' ) ) exec @RowsMoved = QBM_PDBQCS_CurrentMoveSlot @DBQueueToMove25, @SlotNumberSource, @SlotnumberTarget  delete @DBQueueCurrent from @DBQueueCurrent cu join @DBQueueToMove mo on cu.UID_DialogDBQueue = mo.UID_DialogDBQueue26 select @resetted += @RowsMoved  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: end 27