Back to OIM Explorer

dbo.QBM_PXDateSubItemUpdate

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. References QBM_PJobCreate*

Source: sandbox-db sys.sql_modules

Source size: 4.211 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.
  • Object-layer bridge detected through QBM_PJobCreate helper usage.

Relations

  • References QBM_PJobCreate*

Typed Edges

  • references source dbo.QBM_FCVDatetimeToString source text reference
  • references source dbo.QBM_FCVDatetimeToStringUTC source text reference
  • references source dbo.QBM_FCVIntToString 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_FGIPrimaryKeyName source text reference
  • references source dbo.QBM_FGIPrimaryKeyName_F source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_FGITableName source text reference
  • references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PJobCreate source text reference
  • references source dbo.QBM_PJobCreate_HOUpdate source text reference
  • references source dbo.QBM_PJobCreate_HOUpdate_B source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PSessionErrorClean source text reference

References

Referenced By

Complete Source

SQL174 lines
1CREATE PROCEDURE QBM_PXDateSubItemUpdate(2  @SlotNumber int,3  @FullSyncVariable int = 04)5AS6BEGIN7  DECLARE @UID_dialogtable varchar(38)8  DECLARE @TableName varchar(30)9  DECLARE @PKColumnName varchar(39)10  DECLARE @Whereclause nvarchar(max)11  DECLARE @SQLCmd nvarchar(max)12  DECLARE @JustNow varchar(64) = dbo.QBM_FCVDatetimeToStringUTC(getutcdate())13  DECLARE @MyXUser nvarchar(64) = object_name(@@procid)14  DECLARE @MitEvent BIT = 015  DECLARE @GenProcID varchar(38)16  DECLARE @WhereClauseAdditional nvarchar(max)17  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 @DebugLevel varchar(1) = 'W'23  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent24  DECLARE @JournalMessage nvarchar(4000)25  SET XACT_ABORT OFF26  BEGIN TRY27    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,28    UID_Parameter,29    UID_SubParameter,30    GenProcID)31    SELECT32      UID_DialogDBQueue,33      UID_Parameter,34      UID_SubParameter,35      GenProcID36    FROM QBMDBQueueCurrent cu37      WITH(readpast)38    WHERE39      cu.SlotNumber = @SlotNumber40    IF @@rowcount = 041    BEGIN42      GOTO EndLabel43    END44    SELECT45      @WhereClauseAdditional = N ' isnull(XDateSubItem, ''1899-12-30'') < ''' + @JustNow + ''' '46    INSERT INTO @ElementBuffer(UID1,47    UID2)48    SELECT49      cu.UID_SubParameter,50      cu.GenProcID51    FROM @DBQueueCurrent cu52    GROUP BY cu.UID_SubParameter,53    cu.GenProcID54    SELECT @ElementCount = @@ROWCOUNT55    SELECT @ElementIndex = 156    WHILE @ElementIndex <= @ElementCount57    BEGIN58      SELECT59        TOP 1 @UID_DialogTable = bu.UID1,60        @GenProcID = bu.UID261      FROM @ElementBuffer bu62      WHERE63        bu.ElementIndex = @ElementIndex64      IF EXISTS(65        SELECT TOP 1 166        FROM JobEventGen e67        JOIN QBMEvent et68          ON e.UID_QBMEvent = et.UID_QBMEvent69        WHERE70          et.UID_DialogTable = @UID_dialogtable AND et.EventName = 'update')71      BEGIN72        SELECT @MitEvent = 173      END74      SELECT @TableName = dbo.QBM_FGITableName(@uid_dialogtable)75      IF isnull(@TableName,76      '') = ''77      BEGIN78        SELECT79          @JournalMessage = CONCAT('#LDS#Invalid UID_DialogTable: "{0}".|',80          @uid_dialogtable,81          '|')82        EXEC QBM_PJournal @JournalMessage,83          @@procid,84        'E',85          @DebugLevel86        SELECT @ElementIndex += 1 CONTINUE87      END88      SELECT89        @PKColumnName = dbo.QBM_FGIPrimaryKeyName_F(@TableName,90        1)91      SELECT92        @Whereclause = @PKColumnName + ' in (select UID_Parameter /* the element */93														from QBMDBQueueCurrent with (readpast)94														where Slotnumber = '95        + str(@Slotnumber) + '96														and UID_SubParameter = ''' + @UID_DialogTable + ''' 97														and GenProcID = ''' + @GenProcID + '''98													  )99										'100      IF EXISTS(101        SELECT TOP 1 1102        FROM DialogColumn c103        WHERE104          c.uid_DialogTable = @UID_DialogTable AND c.ColumnName = 'XMarkedForDeletion')105      BEGIN106        SELECT107          @Whereclause = @Whereclause + ' and XMarkedForDeletion & ' + dbo.QBM_FCVIntToString(dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|'108          , 0)) + ' = 0'109      END110      IF @FullSyncVariable = 1 OR @MitEvent = 0111      BEGIN112        EXEC QBM_PSessionContextSet 'GenProcID',113          @GenProcID114        EXEC QBM_PSessionContextSet 'XUser',115          @MyXUser116        SELECT117          @SQLCmd = CONCAT('update ',118          @TableName,119          '120					set XDateSubItem = ''',121          @JustNow,122          '''123					',124        CASE dbo.QBM_FGIColumnExistsInSchema(@TableName, 'XDateUpdated')125          WHEN 1 THEN126        ', XDateUpdated = getutcdate(), XUserUpdated =  ''' + object_name(@@procid) + ''''127        ELSE ''128        END,129        '130					where ',131        @Whereclause,132        '133					 and ',134        @WhereClauseAdditional)135        BEGIN TRY136          EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,137            @LockTimeout_ms = DEFAULT,138            @MaxWaitTimeForLock_s = DEFAULT,139            @ProcIDForJournal = DEFAULT,140            @HandleErrorSilent = 0,141            @ExecuteWithTransact = 0142        END TRY143        BEGIN CATCH144          EXEC QBM_PSessionErrorClean145        END CATCH146      END147      ELSE148      BEGIN149        EXEC dbo.QBM_PJobCreate_HOUpdate_B @TableName,150          @Whereclause,151          @GenProcID,152          @p1 = 'XDateSubItem',153          @v1 = @JustNow,154          @WhereClauseAdditional = @WhereClauseAdditional,155          @AdditionalObjectKeysAffected = DEFAULT156      END157      SELECT @ElementIndex += 1158    END159  END TRY160  BEGIN CATCH161    EXEC QBM_PSessionErrorAdd DEFAULT162    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()163    RAISERROR(@Rethrow,164    18,165    1)166      WITH NOWAIT167  END CATCH168  EndLabel:169  EXEC QBM_PSessionContextSet 'GenProcID',170    @GenProcID_R171  EXEC QBM_PSessionContextSet 'XUser',172    @XUser_R173  RETURN174END
Open raw exported source
SQL ยท Raw37 lines
1      create   procedure QBM_PXDateSubItemUpdate (@SlotNumber int , @FullSyncVariable int = 0 ) AS begin declare @UID_dialogtable varchar(38) declare2 @TableName varchar(30) declare @PKColumnName varchar(39) declare @Whereclause nvarchar(max) declare @SQLCmd nvarchar(max) declare @JustNow varchar(64)3 = dbo.QBM_FCVDatetimeToStringUTC(getutcdate()) declare @MyXUser nvarchar(64) = object_name(@@procid) declare @MitEvent bit = 0 declare @GenProcID varchar4(38) declare @WhereClauseAdditional nvarchar(max) declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext5('XUser') declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @DebugLevel varchar(1) = 'W' declare @DBQueueCurrent6 QBM_YDBQueueCurrent declare @JournalMessage nvarchar(4000) SET XACT_ABORT OFF BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, 7UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber8 = @SlotNumber if @@rowcount = 0 begin goto EndLabel end  select @WhereClauseAdditional = N' isnull(XDateSubItem, ''1899-12-30'') < ''' + @JustNow + ''' '9 insert into @ElementBuffer (UID1, UID2) select cu.UID_SubParameter , cu.GenProcID from @DBQueueCurrent cu group by cu.UID_SubParameter  , cu.GenProcID10 select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @UID_DialogTable = bu.UID1 , @GenProcID11 = bu.UID2 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex if exists(select top 1 1 from JobEventGen e join QBMEvent et on e.UID_QBMEvent 12= et.UID_QBMEvent where et.UID_DialogTable = @UID_dialogtable and et.EventName = 'update' ) begin select @MitEvent = 1 end select @TableName = dbo.QBM_FGITableName13(@uid_dialogtable) if isnull(@TableName, '') = '' begin select @JournalMessage = concat('#LDS#Invalid UID_DialogTable: "{0}".|' , @uid_dialogtable, '|'14) exec QBM_PJournal @JournalMessage, @@procid,'E', @DebugLevel select @ElementIndex += 1 continue end select @PKColumnName = dbo.QBM_FGIPrimaryKeyName_F15(@TableName, 1) select @Whereclause = @PKColumnName + ' in (select UID_Parameter /* the element */16														from QBMDBQueueCurrent with (readpast)17														where Slotnumber = '18 + str(@Slotnumber) + '19														and UID_SubParameter = ''' + @UID_DialogTable + ''' 20														and GenProcID = ''' + @GenProcID + '''21													  )22										'23  if exists (select top 1 1 from DialogColumn c where c.uid_DialogTable = @UID_DialogTable and c.ColumnName = 'XMarkedForDeletion' ) begin  select @Whereclause24 = @Whereclause + ' and XMarkedForDeletion & ' + dbo.QBM_FCVIntToString( dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|', 0)) + ' = 0' end   if @FullSyncVariable25 = 1 or @MitEvent = 0 begin exec QBM_PSessionContextSet 'GenProcID', @GenProcID exec QBM_PSessionContextSet 'XUser', @MyXUser select @SQLCmd = concat('update '26 , @TableName , '27					set XDateSubItem = ''' , @JustNow , '''28					', case dbo.QBM_FGIColumnExistsInSchema(@TableName, 'XDateUpdated') when 1 then ', XDateUpdated = getutcdate(), XUserUpdated =  '''29 + object_name(@@procid) + '''' else '' end, '30					where ' , @Whereclause , '31					 and ' , @WhereClauseAdditional )  BEGIN TRY exec QBM_PExecuteSQLWithRetry_LLP32 @SQLStatement = @SQLcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = default , @HandleErrorSilent = 0 , @ExecuteWithTransact33 = 0 END TRY BEGIN CATCH  exec QBM_PSessionErrorClean END CATCH end else begin exec dbo.QBM_PJobCreate_HOUpdate_B @TableName, @Whereclause, @GenProcID 34, @p1 = 'XDateSubItem', @v1 = @JustNow , @WhereClauseAdditional = @WhereClauseAdditional , @AdditionalObjectKeysAffected = DEFAULT end select @ElementIndex35 += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow,36 18, 1) WITH NOWAIT END CATCH EndLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet 'XUser', @XUser_R return end 37