Back to OIM Explorer

dbo.DPR_PMemberShipActionCleanUp

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 1.897 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_FCVObjectkeyToElement source text reference
  • references source dbo.QBM_FGIColumnExists source text reference
  • references source dbo.QBM_FGIColumnExistsInSchema source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL77 lines
1CREATE PROCEDURE DPR_PMemberShipActionCleanUp2AS3BEGIN4  DECLARE @HoursLeft int = 245  DECLARE @SQLCmd nvarchar(max)6  DECLARE @DebugSwitch int = 07  DECLARE @ElementBuffer QBM_YCursorBuffer8  DECLARE @ElementCount int9  DECLARE @ElementIndex int10  SET XACT_ABORT OFF11  BEGIN TRY12    INSERT INTO @ElementBuffer(ContentFull)13    SELECT14      CONCAT('15		update ',16      x.TableName,17      ' set XDateSubItem = GETUTCDATE() ',18    CASE dbo.QBM_FGIColumnExistsInSchema(x.TableName, 'XDateUpdated')19      WHEN 1 THEN20    ', XDateUpdated = getutcdate(), XUserUpdated = ''DPR_PMemberShipActionCleanUp'' '21    ELSE ''22    END,23    ' where XObjectKey = ''',24    x.ObjectKeyBase,25    '''')26    FROM(27    SELECT28      a.ObjectKeyBase,29      dbo.QBM_FCVObjectkeyToElement('TableName', a.ObjectKeyBase) AS TableName30    FROM DPRMemberShipAction a31    JOIN DialogTable t32      WITH(readpast)33      ON a.ObjectKeyBase LIKE '<Key><T>' + t.TableName + '</T>%'34    JOIN DialogColumn c35      WITH(readpast)36      ON t.UID_DialogTable = c.UID_DialogTable AND c.columnname = 'XDateSubItem'37    LEFT38    OUTER39    JOIN QBMElementAffectedByJob ee40      ON a.ObjectKeyBase = ee.ObjectKeyAffected41    WHERE42      ee.ObjectKeyAffected IS NULL AND a.OperationDate < dateadd(HH,(-1) * @HoursLeft, GETUTCDATE())) AS x43    SELECT @ElementCount = @@ROWCOUNT44    SELECT @ElementIndex = 145    WHILE @ElementIndex <= @ElementCount46    BEGIN47      SELECT TOP 1 @SQLCmd = bu.ContentFull48      FROM @ElementBuffer bu49      WHERE50        bu.ElementIndex = @ElementIndex51      IF @DebugSwitch > 052      BEGIN53        print @SQLCmd54      END55      EXEC sp_executesql @SQLCmd56      SELECT @ElementIndex += 157    END58    DELETE DPRMemberShipAction59    FROM DPRMemberShipAction a60    LEFT61    OUTER62    JOIN QBMElementAffectedByJob ee63      ON a.ObjectKeyBase = ee.ObjectKeyAffected64    WHERE65      ee.ObjectKeyAffected IS NULL AND a.OperationDate < dateadd(HH,66    (-1) * @HoursLeft,67    GETUTCDATE())68  END TRY69  BEGIN CATCH70    EXEC QBM_PSessionErrorAdd DEFAULT71    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()72    RAISERROR(@Rethrow,73    18,74    1)75      WITH NOWAIT76  END CATCH77END
Open raw exported source
SQL ยท Raw15 lines
1   create   procedure DPR_PMemberShipActionCleanUp as begin declare @HoursLeft int = 24 declare @SQLCmd nvarchar(max) declare @DebugSwitch int =2 0 declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int SET XACT_ABORT OFF BEGIN TRY  insert into @ElementBuffer3 (ContentFull) select concat( '4		update ' , x.TableName , ' set XDateSubItem = GETUTCDATE() ' , case dbo.QBM_FGIColumnExistsInSchema(x.TableName, 'XDateUpdated'5) when 1 then ', XDateUpdated = getutcdate(), XUserUpdated = ''DPR_PMemberShipActionCleanUp'' ' else '' end , ' where XObjectKey = ''' , x.ObjectKeyBase6 , '''' ) from ( select a.ObjectKeyBase, dbo.QBM_FCVObjectkeyToElement('TableName', a.ObjectKeyBase) as TableName from DPRMemberShipAction a join DialogTable7 t with (readpast)  on a.ObjectKeyBase like '<Key><T>' + t.TableName + '</T>%' join DialogColumn c with (readpast) on t.UID_DialogTable = c.UID_DialogTable8 and c.columnname = 'XDateSubItem' left outer join QBMElementAffectedByJob ee on a.ObjectKeyBase = ee.ObjectKeyAffected where ee.ObjectKeyAffected is null9 and a.OperationDate < dateadd(HH, (-1) * @HoursLeft, GETUTCDATE()) ) as x select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex10 <= @ElementCount begin select top 1 @SQLCmd = bu.ContentFull from @ElementBuffer bu where bu.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print11 @SQLCmd end exec sp_executesql @SQLCmd select @ElementIndex += 1 end   delete DPRMemberShipAction  from DPRMemberShipAction a left outer join QBMElementAffectedByJob12 ee on a.ObjectKeyBase = ee.ObjectKeyAffected where ee.ObjectKeyAffected is null and a.OperationDate < dateadd(HH, (-1) * @HoursLeft, GETUTCDATE()) END13 TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT14 END CATCH end 15