Back to OIM Explorer

dbo.QBM_PMNTableOriginUpdate

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.721 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_FGIBitPatternXOrigin source text reference
  • references source dbo.QBM_FGIColumnExists source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_FGISessionErrorRethrow 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

SQL230 lines
1CREATE PROCEDURE QBM_PMNTableOriginUpdate(2  @TargetTable varchar(30),3  @ElementColumn varchar(30),4  @AssignedElementColumn varchar(30),5  @RestrictionClause nvarchar(max) = ''6)7AS8BEGIN9  DECLARE @GenProcID varchar(38)10  DECLARE @SQLCmd nvarchar(max)11  DECLARE @SQLPattern nvarchar(max)12  DECLARE @ExistsXIsInEffect BIT = 013  DECLARE @ExistsXdate BIT = 014  DECLARE @RestrictionClause_intern nvarchar(max)15  DECLARE @DebugSwitch int = 016  DECLARE @IsCreatePendingChange BIT = 017  DECLARE @Maske varchar(64)18  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')19  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')20  DECLARE @ElementCount int21  DECLARE @ElementIndex int22  DECLARE @MaxWaitTimeForMainOperation float = 5.023  SET XACT_ABORT OFF24  BEGIN TRY25    SELECT @IsCreatePendingChange = sign(t.PendingChangeBehavior & 0x01)26    FROM DialogTable t27    WHERE28      t.TableName = @TargetTable29    SELECT30      @Maske = dbo.QBM_FCVBinaryToString(dbo.QBM_FGIBitPatternXOrigin('|inherit|', 0),31      0)32    SELECT33      @ExistsXIsInEffect = dbo.QBM_FGIColumnExists(@TargetTable,34      'XIsInEffect')35    SELECT36      @ExistsXdate = dbo.QBM_FGIColumnExists(@TargetTable,37      'XDateInserted')38    IF isnull(@RestrictionClause,39    '') = ''40    BEGIN41      SELECT @RestrictionClause_intern = ''42    END43    ELSE44    BEGIN45      SELECT46        @RestrictionClause_intern = CONCAT(' and ( ',47        @RestrictionClause,48        ' ) ')49    END50    SELECT51      @SQLPattern = CONCAT(N '52 if exists (select top 1 1 53			from #QBMDeltaOrigin o54			where o.XOrigin > 055			)56  begin57   update ',58      @TargetTable,59      '60	set XOrigin = x.XOrigin',61    CASE @ExistsXIsInEffect62      WHEN 1 THEN63    '64		, XIsInEffect = x.XIsInEffect'65    ELSE ''66    END,67    CASE @ExistsXdate68      WHEN 1 THEN69    '70		, XDateUpdated = GetUtcDate()71		, XUserUpdated = ''' + OBJECT_NAME(@@procid) + ''''72    ELSE ''73    END,74    '75    from ',76    @TargetTable,77    ' y join #QBMDeltaOrigin x on y.',78    @ElementColumn,79    ' = x.Element80													and y.',81    @AssignedElementColumn,82    ' = x.AssignedElement83													and x.GenProcID = ''@GenProcID''84													and x.XOrigin > 085	where (y.XOrigin <> x.XOrigin',86    CASE @ExistsXIsInEffect87      WHEN 1 THEN88    '89		or y.XIsInEffect <> x.XIsInEffect'90    ELSE ''91    END,92    '  )93	',94    @RestrictionClause_intern,95    '96  end97')98    IF @IsCreatePendingChange = 199    BEGIN100      SELECT101        @SQLPattern = CONCAT(@SQLPattern,102        'if exists (select top 1 1 103			from #QBMDeltaOrigin o104			where o.XOrigin = 0105			)106  begin107   update ',108        @TargetTable,109        '110	set XOrigin = 0',111      CASE @ExistsXIsInEffect112        WHEN 1 THEN113      '114		, XIsInEffect = 0'115      ELSE ''116      END,117      CASE @ExistsXdate118        WHEN 1 THEN119      '120		, XDateUpdated = GetUtcDate()121		, XUserUpdated = ''' + OBJECT_NAME(@@procid) + ''''122      ELSE ''123      END,124      '125    from ',126      @TargetTable,127      ' y join #QBMDeltaOrigin x on y.',128      @ElementColumn,129      ' = x.Element130													and y.',131      @AssignedElementColumn,132      ' = x.AssignedElement133													and x.GenProcID = ''@GenProcID''134													and x.XOrigin = 0135	where (y.XOrigin & ',136      @maske,137      ' > 0 )138	  ',139      @RestrictionClause_intern,140      '141  end		142	')143    END144    ELSE145    BEGIN146      SELECT147        @SQLPattern = CONCAT(@SQLPattern,148        'if exists (select top 1 1 149			from #QBMDeltaOrigin o150			where o.XOrigin = 0151			)152  begin153   update ',154        @TargetTable,155        '156	set XOrigin = 0 ',157      CASE @ExistsXdate158        WHEN 1 THEN159      '160		, XDateUpdated = GetUtcDate()161		, XUserUpdated = ''' + OBJECT_NAME(@@procid) + ''''162      ELSE ''163      END,164      ' from ',165      @TargetTable,166      ' y join #QBMDeltaOrigin x on y.',167      @ElementColumn,168      ' = x.Element169													and y.',170      @AssignedElementColumn,171      ' = x.AssignedElement172													and x.GenProcID = ''@GenProcID''173													and x.XOrigin = 0174--	where (y.XOrigin > 0 )175	',176      @RestrictionClause_intern,177      '178  end		179	')180    END181    IF @DebugSwitch > 0182    BEGIN183      print @sqlpattern184    END185    DECLARE @GenProcIDs_CounOrigin QBM_YCursorBuffer186    INSERT INTO @GenProcIDs_CounOrigin(UID1)187    SELECT188      DISTINCT d.GenProcID189    FROM #QBMDeltaOrigin d190    SELECT @ElementCount = @@ROWCOUNT191    SELECT @ElementIndex = 1192    WHILE @ElementIndex <= @ElementCount193    BEGIN194      SELECT TOP 1 @GenProcID = bu.UID1195      FROM @GenProcIDs_CounOrigin bu196      WHERE197        bu.ElementIndex = @ElementIndex198      EXEC QBM_PSessionContextSet 'GenProcID',199        @GenProcID200      SELECT201        @SQLCmd = REPLACE(@SQLPattern,202        '@GenProcID',203        @GenProcID)204      IF @DebugSwitch > 0205      BEGIN206        print @SQLCmd207      END208      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,209        @LockTimeout_ms = DEFAULT,210        @MaxWaitTimeForLock_s = @MaxWaitTimeForMainOperation,211        @ProcIDForJournal = DEFAULT,212        @HandleErrorSilent = 0213      SELECT @ElementIndex += 1214    END215  END TRY216  BEGIN CATCH217    EXEC QBM_PSessionErrorAdd DEFAULT218    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()219    RAISERROR(@Rethrow,220    18,221    1)222      WITH NOWAIT223  END CATCH224  endLabel:225  EXEC QBM_PSessionContextSet 'GenProcID',226    @GenProcID_R227  EXEC QBM_PSessionContextSet 'XUser',228    @XUser_R229  RETURN230END
Open raw exported source
SQL ยท Raw81 lines
1   create   procedure QBM_PMNTableOriginUpdate (@TargetTable varchar(30) , @ElementColumn varchar(30) , @AssignedElementColumn varchar(30)   , @RestrictionClause2 nvarchar(max) = '' ) as begin declare @GenProcID varchar(38) declare @SQLCmd nvarchar(max) declare @SQLPattern nvarchar(max) declare @ExistsXIsInEffect3 bit = 0 declare @ExistsXdate bit = 0 declare @RestrictionClause_intern nvarchar(max) declare @DebugSwitch int = 0 declare @IsCreatePendingChange bit =4 0 declare @Maske varchar(64) declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext5('XUser') declare @ElementCount int declare @ElementIndex int declare @MaxWaitTimeForMainOperation float = 5.0 SET XACT_ABORT OFF BEGIN TRY select @IsCreatePendingChange6 = sign(t.PendingChangeBehavior & 0x01) from DialogTable t where t.TableName = @TargetTable select @Maske = dbo.QBM_FCVBinaryToString( dbo.QBM_FGIBitPatternXOrigin7('|inherit|', 0), 0) select @ExistsXIsInEffect = dbo.QBM_FGIColumnExists(@TargetTable, 'XIsInEffect') select @ExistsXdate = dbo.QBM_FGIColumnExists(@TargetTable8, 'XDateInserted') if isnull (@RestrictionClause, '') = '' begin select @RestrictionClause_intern = '' end else begin select @RestrictionClause_intern 9= concat(' and ( ' , @RestrictionClause , ' ) ') end select @SQLPattern = concat(N'10 if exists (select top 1 1 11			from #QBMDeltaOrigin o12			where o.XOrigin > 013			)14  begin15   update '16 , @TargetTable , '17	set XOrigin = x.XOrigin' , case @ExistsXIsInEffect when 1 then '18		, XIsInEffect = x.XIsInEffect' else '' end , case @ExistsXdate19 when 1 then '20		, XDateUpdated = GetUtcDate()21		, XUserUpdated = ''' + OBJECT_NAME(@@procid) + '''' else '' end , '22    from ' , @TargetTable , ' y join #QBMDeltaOrigin x on y.'23 , @ElementColumn , ' = x.Element24													and y.' , @AssignedElementColumn , ' = x.AssignedElement25													and x.GenProcID = ''@GenProcID''26													and x.XOrigin > 027	where (y.XOrigin <> x.XOrigin'28 , case @ExistsXIsInEffect when 1 then '29		or y.XIsInEffect <> x.XIsInEffect' else '' end , '  )30	' , @RestrictionClause_intern , '31  end32' ) if @IsCreatePendingChange33 = 1 begin  select @SQLPattern = concat(@SQLPattern , 'if exists (select top 1 1 34			from #QBMDeltaOrigin o35			where o.XOrigin = 036			)37  begin38   update '39 , @TargetTable , '40	set XOrigin = 0' ,  case @ExistsXIsInEffect when 1 then '41		, XIsInEffect = 0' else '' end , case @ExistsXdate when 1 then '42		, XDateUpdated = GetUtcDate()43		, XUserUpdated = '''44 + OBJECT_NAME(@@procid) + '''' else '' end , '45    from ' , @TargetTable , ' y join #QBMDeltaOrigin x on y.' , @ElementColumn , ' = x.Element46													and y.'47 , @AssignedElementColumn , ' = x.AssignedElement48													and x.GenProcID = ''@GenProcID''49													and x.XOrigin = 050	where (y.XOrigin & '51 , @maske , ' > 0 )52	  ' , @RestrictionClause_intern , '53  end		54	' ) end else  begin  select @SQLPattern = concat(@SQLPattern , 'if exists (select top 1 1 55			from #QBMDeltaOrigin o56			where o.XOrigin = 057			)58  begin59   update '60 , @TargetTable , '61	set XOrigin = 0 ' , case @ExistsXdate when 1 then '62		, XDateUpdated = GetUtcDate()63		, XUserUpdated = ''' + OBJECT_NAME(@@procid64) + '''' else '' end , ' from ' , @TargetTable , ' y join #QBMDeltaOrigin x on y.' , @ElementColumn , ' = x.Element65													and y.' , @AssignedElementColumn66 , ' = x.AssignedElement67													and x.GenProcID = ''@GenProcID''68													and x.XOrigin = 069--	where (y.XOrigin > 0 )70	' , @RestrictionClause_intern71 , '72  end		73	' ) end  if @DebugSwitch > 0 begin print @sqlpattern end declare @GenProcIDs_CounOrigin QBM_YCursorBuffer insert into @GenProcIDs_CounOrigin74 (UID1) select distinct d.GenProcID from #QBMDeltaOrigin d select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount75 begin select top 1 @GenProcID = bu.UID1 from @GenProcIDs_CounOrigin bu where bu.ElementIndex = @ElementIndex exec QBM_PSessionContextSet 'GenProcID', 76@GenProcID select @SQLCmd = REPLACE(@SQLPattern, '@GenProcID', @GenProcID) if @DebugSwitch > 0 begin print @SQLCmd end exec QBM_PExecuteSQLWithRetry_LLP77 @SQLStatement = @SQLcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = @MaxWaitTimeForMainOperation , @ProcIDForJournal = default , @HandleErrorSilent78 = 0 select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow79() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet 'XUser'80, @XUser_R return end 81