Back to OIM Explorer

dbo.QBM_PCheckSameCase_38

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.755 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_FGIColumnExists source text reference
  • references source dbo.QBM_FGIColumnExistsInSchema source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL186 lines
1CREATE PROCEDURE QBM_PCheckSameCase_382AS3BEGIN4  DECLARE @SQLcmd nvarchar(max)5  DECLARE @cmdwork nvarchar(max)6  DECLARE @parenttable nvarchar(64),7  @childtable nvarchar(64),8  @ParentColumn nvarchar(64),9  @ChildColumn nvarchar(64)10  DECLARE @changed BIT11  DECLARE @CountItems int12  DECLARE @DebugLevel char(1) = 'W'13  DECLARE @Debugmessage nvarchar(4000)14  DECLARE @DebugSwitch int = 015  DECLARE @ElementBuffer QBM_YCursorBuffer16  DECLARE @ElementCount int17  DECLARE @ElementIndex int18  DECLARE @XUser nvarchar(64) = object_name(@@procid)19  DECLARE @Xdate datetime = getutcdate()20  SET XACT_ABORT OFF21  BEGIN TRY22    UPDATE QBMRelation23    SET XMarkedForDeletion = 0,24    XDateUpdated = @Xdate,25    XUserUpdated = @XUser26    WHERE27      isnull(XMarkedForDeletion,28    -1) <> 0 AND QBMRelation.ParentExecuteBy IN('T',29    'D')30    UPDATE QBMRelation31    SET XMarkedForDeletion += 1,32    XDateUpdated = @Xdate,33    XUserUpdated = @XUser34    FROM QBMRelation tr35    WHERE36      EXISTS(37    SELECT TOP 1 138    FROM QBMRelation t139    WHERE40      t1.relationid <> tr.relationid AND tr.uid_childcolumn = tr.uid_parentcolumn AND t1.XMarkedForDeletion = tr.XMarkedForDeletion) AND tr.ParentExecuteBy41    IN('T',42    'D')43    SELECT44      @SQLcmd = N '45			if exists (select top 1 146						 from  @childtable, @parenttable p47						   where @childtable.@childcolumn = p.@parentColumn 48							 and @childtable.@childcolumn <> p.@parentColumn  collate SQL_Latin1_General_CP1_CS_AS /* 35127 CS*/49						)50			 begin51				update @childtable set  @childtable.@childcolumn = p.@parentColumn #xwerte#52				  from  @childtable, @parenttable p53					   where @childtable.@childcolumn = p.@parentColumn 54						 and @childtable.@childcolumn <> p.@parentColumn  collate SQL_Latin1_General_CP1_CS_AS /* 35127 CS*/55			 end56				'57    INSERT INTO @ElementBuffer(Ident1,58    Ident2,59    Ident3,60    ObjectKey1)61    SELECT62      rtrim(tr.parenttable),63      rtrim(tr.childtable),64      rtrim(tr.ParentColumn) AS ParentColumn,65      rtrim(tr.ChildColumn) AS ChildColumn66    FROM QBM_VQBMRelation tr67    JOIN DialogColumn ic68      WITH(readpast)69      ON ic.UID_DialogTable = tr.UID_DialogTableParent AND ic.UID_DialogColumn = tr.UID_ParentColumn AND ic.SchemaDataType LIKE '%char%'70    JOIN dialogtable t171      WITH(readpast)72      ON tr.UID_DialogTableParent = t1.UID_DialogTable AND t1.tabletype IN('T',73    'B')74    JOIN QBM_VSchemaColumns isp75      ON isp.TABLE_NAME = tr.parenttable AND isp.COLUMN_NAME = tr.ParentColumn76    JOIN dialogtable t277      WITH(readpast)78      ON tr.UID_DialogTableChild = t2.UID_DialogTable AND t2.tabletype IN('T',79    'B')80    JOIN QBM_VSchemaColumns isc81      ON isc.TABLE_NAME = tr.ChildTable AND isc.COLUMN_NAME = tr.ChildColumn82    WHERE83      tr.ParentExecuteBy IN('T',84    'D') AND t1.TableName NOT IN(85    SELECT TableName86    FROM QBM_VHeavyLoadTables) AND t2.TableName NOT IN(87    SELECT TableName88    FROM QBM_VHeavyLoadTables)89    ORDER BY tr.XMarkedForDeletion DESC,90    parenttable ASC,91    childtable ASC92    SELECT @ElementCount = @@ROWCOUNT93    SELECT @ElementIndex = 194    WHILE @ElementIndex <= @ElementCount95    BEGIN96      SELECT97        TOP 1 @parenttable = bu.Ident1,98        @childtable = bu.Ident2,99        @ParentColumn = bu.Ident3,100        @ChildColumn = bu.ObjectKey1101      FROM @ElementBuffer bu102      WHERE103        bu.ElementIndex = @ElementIndex104      SELECT @cmdwork = @SQLcmd105      SELECT106        @cmdwork = replace(@cmdwork,107        N '@childtable',108        @childtable)109      SELECT110        @cmdwork = replace(@cmdwork,111        N '@parenttable',112        @parenttable)113      SELECT114        @cmdwork = replace(@cmdwork,115        N '@childcolumn',116        @childcolumn)117      SELECT118        @cmdwork = replace(@cmdwork,119        N '@parentcolumn',120        @parentcolumn)121      SELECT122        @cmdwork = replace(@cmdwork,123        N '#xwerte#',124      CASE125        WHEN dbo.QBM_FGIColumnExistsInSchema(@childtable, 'XDateUpdated') = 1 AND @ChildColumn NOT LIKE 'X[d,u][a,s][t,e][e,r]updated' THEN126      ', ' + @childtable + '.XDateUpdated = getutcdate(), ' +@childtable+ '.XUserUpdated =  ''' + object_name(@@procid) + ''''127      ELSE ''128      END)129      SELECT @changed = 1130      WHILE @changed = 1131      BEGIN132        EXEC @CountItems = QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @cmdwork,133          @LockTimeout_ms = DEFAULT,134          @MaxWaitTimeForLock_s = DEFAULT,135          @ProcIDForJournal = @@procid,136          @HandleErrorSilent = 0137        IF @CountItems > 0138        BEGIN139          SELECT @changed = 1140          SELECT @Debugmessage = @parenttable + ' -> ' + @childtable + ' Changed'141          IF @DebugSwitch > 0142          BEGIN143            print @Debugmessage + str(@CountItems)144          END145          ELSE146          BEGIN147            EXEC QBM_PJournal @Debugmessage,148              @@PROCID,149            'D',150              @DebugLevel151          END152        END153        ELSE154        BEGIN155          SELECT @changed = 0156          SELECT @Debugmessage = @parenttable + ' -> ' + @childtable + ' OK'157          IF @DebugSwitch > 0158          BEGIN159            print @Debugmessage160          END161          ELSE162          BEGIN163            EXEC QBM_PJournal @Debugmessage,164              @@PROCID,165            'T',166              @DebugLevel167          END168        END169      END170      SELECT @ElementIndex += 1171    END172    UPDATE QBMRelation173    SET XMarkedForDeletion = 0174    WHERE175      isnull(XMarkedForDeletion,176    -1) <> 0177  END TRY178  BEGIN CATCH179    EXEC QBM_PSessionErrorAdd DEFAULT180    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()181    RAISERROR(@Rethrow,182    18,183    1)184      WITH NOWAIT185  END CATCH186END
Open raw exported source
SQL ยท Raw41 lines
1     create   procedure QBM_PCheckSameCase_38 as begin declare @SQLcmd nvarchar(max) declare @cmdwork nvarchar(max) declare @parenttable nvarchar2(64), @childtable nvarchar(64), @ParentColumn nvarchar(64), @ChildColumn nvarchar(64) declare @changed bit declare @CountItems int  declare @DebugLevel3 char(1) = 'W' declare @Debugmessage nvarchar(4000) declare @DebugSwitch int = 0 declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare4 @ElementIndex int declare @XUser nvarchar(64) = object_name(@@procid) declare @Xdate datetime = getutcdate() SET XACT_ABORT OFF BEGIN TRY    update QBMRelation5 set XMarkedForDeletion = 0  , XDateUpdated = @Xdate , XUserUpdated = @XUser where isnull(XMarkedForDeletion, -1) <> 0  and QBMRelation.ParentExecuteBy6 in ('T', 'D') update QBMRelation set XMarkedForDeletion += 1 , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMRelation tr where exists (select 7top 1 1 from QBMRelation t1 where t1.relationid <> tr.relationid and tr.uid_childcolumn = tr.uid_parentcolumn and t1.XMarkedForDeletion = tr.XMarkedForDeletion8 )  and tr.ParentExecuteBy in ('T', 'D')  select @SQLcmd = N'9			if exists (select top 1 110						 from  @childtable, @parenttable p11						   where @childtable.@childcolumn = p.@parentColumn 12							 and @childtable.@childcolumn <> p.@parentColumn  collate SQL_Latin1_General_CP1_CS_AS /* 35127 CS*/13						)14			 begin15				update @childtable set  @childtable.@childcolumn = p.@parentColumn #xwerte#16				  from  @childtable, @parenttable p17					   where @childtable.@childcolumn = p.@parentColumn 18						 and @childtable.@childcolumn <> p.@parentColumn  collate SQL_Latin1_General_CP1_CS_AS /* 35127 CS*/19			 end20				'21  insert into @ElementBuffer (Ident1, Ident2, Ident3, ObjectKey1) select rtrim(tr.parenttable), rtrim(tr.childtable), rtrim(tr.ParentColumn) as ParentColumn22, rtrim(tr.ChildColumn) as ChildColumn from QBM_VQBMRelation tr    join DialogColumn ic with (readpast) on ic.UID_DialogTable = tr.UID_DialogTableParent23 and ic.UID_DialogColumn = tr.UID_ParentColumn and ic.SchemaDataType like '%char%'  join dialogtable t1 with (readpast) on tr.UID_DialogTableParent = t1.UID_DialogTable24 and t1.tabletype in ('T', 'B') join QBM_VSchemaColumns isp on isp.TABLE_NAME = tr.parenttable and isp.COLUMN_NAME = tr.ParentColumn join dialogtable t225 with (readpast) on tr.UID_DialogTableChild = t2.UID_DialogTable and t2.tabletype in ('T', 'B') join QBM_VSchemaColumns isc on isc.TABLE_NAME = tr.ChildTable26 and isc.COLUMN_NAME = tr.ChildColumn where tr.ParentExecuteBy in ('T', 'D')  and t1.TableName not in (select TableName from QBM_VHeavyLoadTables  ) and27 t2.TableName not in (select TableName from QBM_VHeavyLoadTables  ) order by tr.XMarkedForDeletion desc, parenttable asc, childtable asc select @ElementCount28 = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @parenttable = bu.Ident1 , @childtable = bu.Ident2 , @ParentColumn29 = bu.Ident3 , @ChildColumn = bu.ObjectKey1 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex select @cmdwork = @SQLcmd select @cmdwork = replace30(@cmdwork, N'@childtable', @childtable) select @cmdwork = replace(@cmdwork, N'@parenttable', @parenttable) select @cmdwork = replace(@cmdwork, N'@childcolumn'31, @childcolumn) select @cmdwork = replace(@cmdwork, N'@parentcolumn', @parentcolumn) select @cmdwork = replace(@cmdwork, N'#xwerte#', case when dbo.QBM_FGIColumnExistsInSchema32(@childtable, 'XDateUpdated') = 1 and @ChildColumn not like 'X[d,u][a,s][t,e][e,r]updated' then ', ' + @childtable +'.XDateUpdated = getutcdate(), '+@childtable+33'.XUserUpdated =  ''' + object_name(@@procid) + '''' else '' end ) select @changed = 1   while @changed = 1 begin  exec @CountItems = QBM_PExecuteSQLWithRetry_LLP34 @SQLStatement = @cmdwork , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 if @CountItems35 > 0 begin select @changed = 1 select @Debugmessage = @parenttable + ' -> ' + @childtable + ' Changed' if @DebugSwitch > 0 begin print @Debugmessage + 36str(@CountItems) end else begin exec QBM_PJournal @Debugmessage, @@PROCID, 'D', @DebugLevel end end else begin select @changed = 0 select @Debugmessage37 = @parenttable + ' -> ' + @childtable + ' OK' if @DebugSwitch > 0 begin print @Debugmessage end else begin exec QBM_PJournal @Debugmessage, @@PROCID, 38'T', @DebugLevel end end end select @ElementIndex += 1 end  update QBMRelation set XMarkedForDeletion = 0 where isnull(XMarkedForDeletion, -1) <> 0 END39 TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT40 END CATCH end 41