Back to OIM Explorer

dbo.QBM_ZXMarkedForDeletionPush

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.343 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_FCVBinaryToString source text reference
  • references source dbo.QBM_FGIBitPatternXMarkedForDel source text reference
  • references source dbo.QBM_FGIColumnExists source text reference
  • references source dbo.QBM_FGIColumnExistsInSchema source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_FGITableName source text reference
  • references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL178 lines
1CREATE PROCEDURE QBM_ZXMarkedForDeletionPush(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @UID_DialogTable varchar(38)7  DECLARE @ParentTableName varchar(30)8  DECLARE @ChildTableName varchar(30)9  DECLARE @ParentColumnName varchar(30)10  DECLARE @ChildColumnName varchar(30)11  DECLARE @GenProcID varchar(38)12  DECLARE @MyXUser nvarchar(64) = object_name(@@procid)13  DECLARE @cmd nvarchar(max)14  DECLARE @PatternNeg varchar(64)15  DECLARE @Pattern varchar(64)16  DECLARE @DebugSwitch int = 017  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')18  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')19  DECLARE @ElementBuffer QBM_YCursorBuffer20  DECLARE @ElementCount int21  DECLARE @ElementIndex int22  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent23  BEGIN TRY24    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,25    UID_Parameter,26    UID_SubParameter,27    GenProcID)28    SELECT29      UID_DialogDBQueue,30      UID_Parameter,31      UID_SubParameter,32      GenProcID33    FROM QBMDBQueueCurrent cu34      WITH(readpast)35    WHERE36      cu.SlotNumber = @SlotNumber37    IF @@rowcount = 038    BEGIN39      GOTO EndLabel40    END41    SELECT42      @PatternNeg = dbo.QBM_FCVBinaryToString(CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|Delay|OutStanding|',43      1)),44      0)45    SELECT46      @Pattern = dbo.QBM_FCVBinaryToString(CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|',47      0)),48      0)49    IF @DebugSwitch > 050    BEGIN51      print @PatternNeg print @Pattern52    END53    INSERT INTO @ElementBuffer(UID1,54    UID2,55    Ident1,56    Ident2,57    Ident3,58    ObjectKey1)59    SELECT60      cu.UID_SubParameter,61      MAX(cu.GenProcID),62      dbo.QBM_FGITableName(cu.UID_SubParameter) AS ParentTableName,63      cpk.ColumnName AS ParentColumnName,64      tc.TableName AS ChildTableName,65      cci.ColumnName AS ChildColumnName66    FROM @DBQueueCurrent cu67    JOIN DialogColumn cpk68      WITH(readpast)69      ON cu.UID_SubParameter = cpk.UID_DialogTable AND cpk.IsPKMember = 170    JOIN DialogColumn ca71      WITH(readpast)72      ON cu.UID_SubParameter = ca.UID_DialogTable AND ca.ColumnName = 'XDateSubItem'73    JOIN DialogColumn cx74      WITH(readpast)75      ON cu.UID_SubParameter = cx.UID_DialogTable AND cx.ColumnName = 'XMarkedForDeletion'76    JOIN QBMRelation r77      WITH(readpast)78      ON cpk.UID_DialogColumn = r.UID_ParentColumn AND r.IsForUpdateXDateSubItem = 179    JOIN DialogColumn cci80      WITH(readpast)81      ON r.UID_ChildColumn = cci.UID_DialogColumn82    JOIN DialogTable tc83      WITH(readpast)84      ON cci.UID_DialogTable = tc.UID_DialogTable85    JOIN DialogColumn ccx86      WITH(readpast)87      ON tc.UID_DialogTable = ccx.UID_DialogTable AND ccx.ColumnName = 'XMarkedForDeletion'88    GROUP BY cu.UID_SubParameter,89    cpk.ColumnName,90    tc.TableName,91    cci.ColumnName92    SELECT @ElementCount = @@ROWCOUNT93    SELECT @ElementIndex = 194    WHILE @ElementIndex <= @ElementCount95    BEGIN96      SELECT97        TOP 1 @UID_DialogTable = bu.UID1,98        @GenProcID = bu.UID2,99        @ParentTableName = bu.Ident1,100        @ParentColumnName = bu.Ident2,101        @ChildTableName = bu.Ident3,102        @ChildColumnName = bu.ObjectKey1103      FROM @ElementBuffer bu104      WHERE105        bu.ElementIndex = @ElementIndex106      SELECT107        @cmd = CONCAT('108	updat',109        'e ',110        @ChildTableName,111        '112		set XMarkedForDeletion = (z.XMarkedForDeletion )| (x.XMarkedForDeletion & ',113        @Pattern,114        ') ',115      CASE116        WHEN dbo.QBM_FGIColumnExistsInSchema(@ChildTableName, 'XDateUpdated') = 1 THEN117      CONCAT('', '	--34101118								, XDateUpdated = getutcdate()119								, XUserUpdated = ''', object_name(@@procid),120      '''121								')122      ELSE ''123      END,124      ' from QBMDBQueueCurrent cu with (readpast) join ',125      @ParentTableName,126      '  x on cu.UID_SubParameter = ''',127      @UID_DialogTable,128      '''129													and cu.UID_Parameter = x.',130      @ParentColumnName,131      '132													and cu.SlotNumber = ',133      str(@SlotNumber),134      ' 135								join ',136      @ChildTableName,137      ' z on z.',138      @ChildColumnName,139      ' = x.',140      @ParentColumnName,141      '142		where (z.XMarkedForDeletion & ',143      @Pattern,144      ') 145			<>  (x.XMarkedForDeletion & ',146      @Pattern,147      ')								148		')149      IF @DebugSwitch > 0150      BEGIN151        print @cmd152      END153      EXEC QBM_PSessionContextSet 'GenProcID',154        @GenProcID155      EXEC QBM_PSessionContextSet 'XUser',156        @MyXUser157      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @cmd,158        @LockTimeout_ms = DEFAULT,159        @MaxWaitTimeForLock_s = DEFAULT,160        @ProcIDForJournal = @@procid,161        @HandleErrorSilent = 0162      SELECT @ElementIndex += 1163    END164  END TRY165  BEGIN CATCH166    EXEC QBM_PSessionErrorAdd DEFAULT167    RAISERROR('',168    18,169    1)170      WITH NOWAIT171  END CATCH172  EndLabel:173  EXEC QBM_PSessionContextSet 'GenProcID',174    @GenProcID_R175  EXEC QBM_PSessionContextSet 'XUser',176    @XUser_R177  RETURN178END
Open raw exported source
SQL ยท Raw40 lines
1        create   procedure QBM_ZXMarkedForDeletionPush (@SlotNumber int ) AS begin declare @UID_DialogTable varchar(38) declare @ParentTableName2 varchar(30) declare @ChildTableName varchar(30) declare @ParentColumnName varchar(30) declare @ChildColumnName varchar(30) declare @GenProcID varchar(383) declare @MyXUser nvarchar(64) = object_name(@@procid) declare @cmd nvarchar(max) declare @PatternNeg varchar(64) declare @Pattern varchar(64) declare4 @DebugSwitch int = 0 declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser'5) declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @DBQueueCurrent QBM_YDBQueueCurrent BEGIN TRY insert6 into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID7 from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end select @PatternNeg = dbo.QBM_FCVBinaryToString8( CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|Delay|OutStanding|', 1)), 0) select @Pattern = dbo.QBM_FCVBinaryToString( CONVERT(varbinary, 9dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|', 0)), 0) if @DebugSwitch > 0 begin print @PatternNeg print @Pattern end insert into @ElementBuffer (10 UID1 , UID2  , Ident1  , Ident2  , Ident3  , ObjectKey1  ) select cu.UID_SubParameter  , MAX(cu.GenProcID) , dbo.QBM_FGITableName(cu.UID_SubParameter 11 ) as ParentTableName , cpk.ColumnName as ParentColumnName , tc.TableName as ChildTableName , cci.ColumnName as ChildColumnName from @DBQueueCurrent cu12 join DialogColumn cpk with (readpast) on cu.UID_SubParameter  = cpk.UID_DialogTable and cpk.IsPKMember = 1  join DialogColumn ca with (readpast) on cu.UID_SubParameter13  = ca.UID_DialogTable and ca.ColumnName = 'XDateSubItem'  join DialogColumn cx with (readpast) on cu.UID_SubParameter  = cx.UID_DialogTable and cx.ColumnName14 = 'XMarkedForDeletion' join QBMRelation r with (readpast) on cpk.UID_DialogColumn = r.UID_ParentColumn and r.IsForUpdateXDateSubItem = 1 join DialogColumn15 cci with (readpast) on r.UID_ChildColumn = cci.UID_DialogColumn join DialogTable tc with (readpast) on cci.UID_DialogTable = tc.UID_DialogTable  join 16DialogColumn ccx with (readpast) on tc.UID_DialogTable = ccx.UID_DialogTable and ccx.ColumnName = 'XMarkedForDeletion' group by cu.UID_SubParameter  , 17cpk.ColumnName, tc.TableName, cci.ColumnName select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select18 top 1 @UID_DialogTable = bu.UID1 , @GenProcID = bu.UID2  , @ParentTableName = bu.Ident1  , @ParentColumnName = bu.Ident2  , @ChildTableName = bu.Ident319  , @ChildColumnName = bu.ObjectKey1  from @ElementBuffer bu where bu.ElementIndex = @ElementIndex select @cmd = concat('20	updat','e ' , @ChildTableName21 , '22		set XMarkedForDeletion = (z.XMarkedForDeletion )| (x.XMarkedForDeletion & ' , @Pattern , ') ' , case when dbo.QBM_FGIColumnExistsInSchema(@ChildTableName23, 'XDateUpdated') = 1 then concat('' , '	--3410124								, XDateUpdated = getutcdate()25								, XUserUpdated = ''', object_name(@@procid) , '''26								'27) else '' end , ' from QBMDBQueueCurrent cu with (readpast) join ' , @ParentTableName , '  x on cu.UID_SubParameter = ''' , @UID_DialogTable , '''28													and cu.UID_Parameter = x.'29 , @ParentColumnName , '30													and cu.SlotNumber = ' , str(@SlotNumber) , ' 31								join ' , @ChildTableName , ' z on z.' , @ChildColumnName 32, ' = x.' , @ParentColumnName , '33		where (z.XMarkedForDeletion & ' , @Pattern , ') 34			<>  (x.XMarkedForDeletion & ' , @Pattern , ')								35		' ) 36if @DebugSwitch > 0 begin print @cmd end exec QBM_PSessionContextSet 'GenProcID', @GenProcID exec QBM_PSessionContextSet 'XUser', @MyXUser exec QBM_PExecuteSQLWithRetry_LLP37 @SQLStatement = @cmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 select @ElementIndex38 += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH EndLabel: exec QBM_PSessionContextSet 'GenProcID'39, @GenProcID_R exec QBM_PSessionContextSet 'XUser', @XUser_R return end 40