dbo.QBM_PBufferT_ProcessTable
Stored ProcedureSQL_STORED_PROCEDURESandbox DB
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_FCVDatetimeToString source text reference
- references source dbo.QBM_FCVStringToIndent source text reference
- references source dbo.QBM_FGISessionErrorRethrow source text reference
- references source dbo.QBM_FSQTableRowOwnedByModule source text reference
- references source dbo.QBM_FSQTableRowOwnedByModuleNL source text reference
- references source dbo.QBM_PBufferT_ProcessInsert source text reference
- references source dbo.QBM_PBufferT_ProcessUpdate source text reference
- references source dbo.QBM_PJournal source text reference
- references source dbo.QBM_PSessionErrorAdd source text reference
- references source dbo.QBM_PTriggersInactiveForTable source text reference
References
- dbo.QBM_FCVDatetimeToString
- dbo.QBM_FCVStringToIndent
- dbo.QBM_FGISessionErrorRethrow
- dbo.QBM_FSQTableRowOwnedByModule
- dbo.QBM_FSQTableRowOwnedByModuleNL
- dbo.QBM_PBufferT_ProcessInsert
- dbo.QBM_PBufferT_ProcessUpdate
- dbo.QBM_PJournal
- dbo.QBM_PSessionErrorAdd
- dbo.QBM_PTriggersInactiveForTable
Referenced By
Complete Source
1CREATE PROCEDURE QBM_PBufferT_ProcessTable(2 @ModuleName varchar(3),3 @TableName varchar(30),4 @PurgeMode BIT = 05)6AS7BEGIN8 DECLARE @columnname varchar(30)9 DECLARE @SQLCmd nvarchar(max)10 DECLARE @CountUpcomingRows int11 DECLARE @DebugSwitch int = 012 DECLARE @DebugLevel varchar(1) = 'W'13 DECLARE @msg nvarchar(4000)14 DECLARE @ErrorMessage nvarchar(4000)15 DECLARE @ErrorSeverity int16 DECLARE @ErrorState int17 DECLARE @ObjectsToDelete QBM_YParameterList18 DECLARE @UID_Module varchar(38)19 DECLARE @GenprocID varchar(38)20 DECLARE @RowCondition_Table nvarchar(max)21 DECLARE @AreRowsToUpdate int = 022 DECLARE @AreRowsToInsert int = 023 DECLARE @AreRowsToDelete int = 024 DECLARE @ColumnsAffected QBM_YCursorBuffer25 DECLARE @ElementCount int26 DECLARE @ElementIndex int27 DECLARE @IsTransportDisabled BIT28 DECLARE @XUser nvarchar(64) = object_name(@@procid)29 DECLARE @Xdate datetime = getutcdate()30 DECLARE @PredecessorsInclNonlinear varchar(2000) = ''31 SET XACT_ABORT OFF32 BEGIN TRY33 SELECT34 TOP 1 @PredecessorsInclNonlinear = CONCAT('(',35 STRING_AGG(CONCAT('''',36 LEFT(x.UID_ModuleDef, 3), '-'''), ','),37 ')')38 FROM(39 SELECT m.UID_ModuleDef40 FROM QBMModuleDef m41 WITH(readpast)42 JOIN QBMModuleDef nl43 WITH(readpast)44 ON nl.ModuleInfoXML LIKE CONCAT('%', '<NonlinearModules>%<Module ModuleID="', m.ModuleName, '"%</NonlinearModules>%')45 WHERE46 nl.UID_ModuleDef = CONCAT(@moduleName, '-Moduledefinition')47 UNION48 SELECT co.UID_ModulePredecessor49 FROM QBMModuleDependCollection co50 WITH(readpast)51 WHERE52 co.UID_ModuleFollower = CONCAT(@moduleName, '-Moduledefinition')53 UNION54 SELECT55 CONCAT(@ModuleName, '-Moduledefinition')) AS x56 SELECT57 TOP 1 @RowCondition_Table = isnull(t.TransportWhereClause,58 ''),59 @IsTransportDisabled = t.IsTransportDisabled60 FROM DialogTable t61 WITH(readpast)62 WHERE63 t.TableName = @TableName64 IF @DebugSwitch > 065 BEGIN66 print '@RowCondition_Table ' + isnull(@RowCondition_Table,67 '#') print '@IsTransportDisabled ' + isnull(str(@IsTransportDisabled),68 '#')69 END70 SELECT @UID_Module = @ModuleName + '-Moduledefinition'71 SELECT @GenprocID = NEWID() drop TABLE72 IF EXISTS #QBMBufferTransferOneTable73 CREATE TABLE #QBMBufferTransferOneTable(74 ColumnName varchar(30) collate database_default,75 ObjectKeyOfRow varchar(138) collate database_default,76 ContentShort nvarchar(400) collate database_default,77 HasContentFull BIT DEFAULT 0,78 ContentFull nvarchar(max) collate database_default79 )80 IF NOT EXISTS(81 SELECT TOP 1 182 FROM sys.tables t83 WITH(readpast)84 WHERE85 t.name = @TableName86 )87 BEGIN88 IF @ModuleName = 'CCC'89 BEGIN90 SELECT @msg = '#LDS#targettable {0} not found|' + @TableName + '|'91 RAISERROR(@msg,92 18,93 194 )95 WITH nowait96 END97 ELSE98 BEGIN99 GOTO endLabel100 END101END102INSERT INTO #QBMBufferTransferOneTable(ColumnName,103ObjectKeyOfRow,104ContentShort,105HasContentFull,106ContentFull107)108SELECT109 ColumnName,110 ObjectkeyOfRow,111 ContentShort,112 0,113 NULL114FROM QBMBufferTransfer115 WITH(readpast116)117WHERE118 TableName = @TableName AND ModuleName = @ModuleName AND HasContentFull = 0119SELECT @CountUpcomingRows = @@ROWCOUNT120INSERT INTO #QBMBufferTransferOneTable(ColumnName,121ObjectKeyOfRow,122ContentShort,123HasContentFull,124ContentFull125)126SELECT127 ColumnName,128 ObjectkeyOfRow,129 ContentShort,130 HasContentFull,131 ContentFull132FROM QBMBufferTransfer133 WITH(readpast134)135WHERE136 TableName = @TableName AND ModuleName = @ModuleName AND HasContentFull = 1137SELECT @CountUpcomingRows += @@ROWCOUNT138CREATE index #XIE1_QBMBufferTransferOneTable139 ON #QBMBufferTransferOneTable(140 ObjectKeyOfRow,141 columnname142) include(ContentShort143)144CREATE index #XIE2_QBMBufferTransferOneTable145 ON #QBMBufferTransferOneTable(146 ColumnName147) INCLUDE(ObjectKeyOfRow,148ContentShort149)150IF @DebugSwitch > 0151BEGIN152 print '-- umgeladen table ' + @TableName + dbo.QBM_FCVDatetimeToString(GETUTCDATE()153)154END155IF EXISTS(156 SELECT TOP 1 1157 FROM #QBMBufferTransferOneTable bt158 JOIN QBMLock l159 WITH(readpast)160 ON l.ObjectKeyOfRow = bt.ObjectKeyOfRow161)162BEGIN163 DELETE #QBMBufferTransferOneTable164 FROM #QBMBufferTransferOneTable bt165 JOIN DialogTable t166 WITH(readpast167)168 ON t.TableName = @TableName169JOIN DialogColumn c170 WITH(readpast171)172 ON t.UID_DialogTable = c.UID_DialogTable AND c.ColumnName = bt.ColumnName173JOIN QBMLock l174 WITH(readpast175)176 ON l.UID_DialogColumn = c.UID_DialogColumn AND l.ObjectKeyOfRow = bt.ObjectKeyOfRow177DELETE #QBMBufferTransferOneTable178FROM #QBMBufferTransferOneTable bt179JOIN QBMLock l180 WITH(readpast181)182 ON l.UID_DialogColumn IS NULL AND l.ObjectKeyOfRow = bt.ObjectKeyOfRow183END184IF @CountUpcomingRows > 0185BEGIN186 SELECT187 @SQLCmd = N 'select top 1 1188 from ' + @TableName + ' x with (readpast) join #QBMBufferTransferOneTable y on x.XObjectKey = y.ObjectKeyOfRow'189 IF @DebugSwitch > 0190 BEGIN191 print @SQLCmd192 END193 BEGIN TRY194 EXEC sp_executesql @sqlcmd195 SELECT @AreRowsToUpdate = @@ROWCOUNT196 END TRY197 BEGIN CATCH198 EXEC QBM_PSessionErrorAdd DEFAULT,199 @SQLCmd200 RAISERROR('',201 18,202 1203 )204 WITH NOWAIT205END CATCH206IF @DebugSwitch > 0207BEGIN208 print '@AreRowsToUpdate: ' + isnull(str(@AreRowsToUpdate),209 'null'210)211END212END213IF @PurgeMode = 1 AND @TableName <> 'QBMNonLinearDepend' AND @IsTransportDisabled = 0214BEGIN215 SELECT216 @SQLCmd = N 'select top 1 1217 from ' + @TableName + ' with (readpast) left outer join #QBMBufferTransferOneTable y on ' + @TableName218 + '.XObjectKey = y.ObjectKeyOfRow 219 where ' + dbo.QBM_FCVStringToIndent(dbo.QBM_FSQTableRowOwnedByModuleNL(@TableName,220 @Modulename,221 '' + @TableName + '',222 @PredecessorsInclNonlinear),223 11224 ) + '225 and y.ObjectKeyOfRow is null226 '227IF @DebugSwitch > 0228BEGIN229 print @SQLCmd230END231BEGIN TRY232 EXEC sp_executesql @sqlcmd233 SELECT @AreRowsToDelete = @@ROWCOUNT234END TRY235BEGIN CATCH236 EXEC QBM_PSessionErrorAdd DEFAULT,237 @SQLCmd238 RAISERROR('',239 18,240 1241)242 WITH NOWAIT243END CATCH244IF @DebugSwitch > 0245BEGIN246 print '@AreRowsToDelete ' + isnull(str(@AreRowsToDelete),247 'null'248)249END250IF @AreRowsToDelete > 0251BEGIN252 EXEC QBM_PTriggersInactiveForTable @TableName,253 'Delete'254END255END256IF @CountUpcomingRows > 0257BEGIN258 SELECT259 @SQLCmd = N 'select top 1 1260 from ' + @TableName + ' x with (readpast) right outer join #QBMBufferTransferOneTable y on x.XObjectKey = y.ObjectKeyOfRow261 where x.XObjectKey is null'262 IF @DebugSwitch > 0263 BEGIN264 print @SQLCmd265 END266 BEGIN TRY267 EXEC sp_executesql @sqlcmd268 SELECT @AreRowsToInsert = @@ROWCOUNT269 END TRY270 BEGIN CATCH271 EXEC QBM_PSessionErrorAdd DEFAULT,272 @SQLCmd273 RAISERROR('',274 18,275 1276 )277 WITH NOWAIT278END CATCH279IF @DebugSwitch > 0280BEGIN281 print '@AreRowsToInsert: ' + isnull(str(@AreRowsToInsert),282 'null'283)284END285IF @AreRowsToInsert > 0286BEGIN287 EXEC QBM_PTriggersInactiveForTable @TableName,288 'Insert'289END290END291IF @CountUpcomingRows = 0 AND @AreRowsToDelete > 0292BEGIN293 SELECT294 @SQLCmd = 'Delete ' + @TableName + '295 where XObjectKey in ( select x.XObjectKey296 from ' + @TableName + ' x with (readpast)297 where '298 + dbo.QBM_FCVStringToIndent(dbo.QBM_FSQTableRowOwnedByModuleNL(@TableName,299 @Modulename,300 'x',301 @PredecessorsInclNonlinear),302 11303 ) + ' 304 )305 and not exists (select top 1 1 306 from QBMLock l with (readpast)307 where l.ObjectKeyOfRow = '308 + @TableName + '.XObjectKey309 )310 '311IF @RowCondition_Table > ' '312BEGIN313 SELECT314 @SQLCmd = @SQLCmd + '315 and ( ' + @RowCondition_Table + '316 )317 '318END319IF @DebugSwitch > 0320BEGIN321 print @SQLcmd322END323BEGIN TRY324 EXEC sp_executesql @SQLcmd325END TRY326BEGIN CATCH327 EXEC QBM_PSessionErrorAdd DEFAULT,328 @SQLCmd329 RAISERROR('',330 18,331 1332)333 WITH NOWAIT334END CATCH335END336IF @CountUpcomingRows > 0 AND @AreRowsToDelete > 0337BEGIN338 SELECT339 @SQLCmd = ' select distinct ' + @TableName + '.XObjectKey340 from ' + @TableName + ' with (readpast) left outer join #QBMBufferTransferOneTable y on '341 + @TableName + '.XObjectKey = y.ObjectKeyOfRow 342 where ' + dbo.QBM_FCVStringToIndent(dbo.QBM_FSQTableRowOwnedByModuleNL(@TableName,343 @Modulename,344 '' + @TableName + '',345 @PredecessorsInclNonlinear),346 11347 ) + '348 and y.ObjectKeyOfRow is null349 and not exists (select top 1 1 350 from QBMLock l with (readpast)351 where l.ObjectKeyOfRow = '352 + @TableName + '.XObjectKey353 )354 '355IF @RowCondition_Table > ' '356BEGIN357 SELECT358 @SQLCmd = @SQLCmd + '359 and ( ' + @RowCondition_Table + '360 )361 '362END363IF @DebugSwitch > 0364BEGIN365 print @SQLcmd366END367BEGIN TRY368 DELETE @ObjectsToDelete369 INSERT INTO @ObjectsToDelete(Parameter1370)371EXEC sp_executesql @SQLcmd372DELETE @ObjectsToDelete373WHERE374 Parameter1 IN(375SELECT ObjectKeyOfRow376FROM QBMLock377 WITH(readpast)378)379SELECT380 @SQLCmd = 'Delete ' + @TableName + '381 from ' + @TableName + ' with (readpast) left outer join #QBMBufferTransferOneTable y on '382 + @TableName + '.XObjectKey = y.ObjectKeyOfRow 383 where ' + dbo.QBM_FCVStringToIndent(dbo.QBM_FSQTableRowOwnedByModuleNL(@TableName,384 @Modulename,385 '' + @TableName + '',386 @PredecessorsInclNonlinear),387 11388 ) + '389 and y.ObjectKeyOfRow is null390 and not exists (select top 1 1 391 from QBMLock l with (readpast)392 where l.ObjectKeyOfRow = '393 + @TableName + '.XObjectKey394 )395 '396IF @RowCondition_Table > ' '397BEGIN398 SELECT399 @SQLCmd = @SQLCmd + '400 and ( ' + @RowCondition_Table + '401 )402 '403END404IF @DebugSwitch > 0405BEGIN406 print @SQLcmd407END408EXEC sp_executesql @SQLcmd409END TRY410BEGIN CATCH411 EXEC QBM_PSessionErrorAdd DEFAULT,412 @SQLCmd413 RAISERROR('',414 18,415 1416)417 WITH NOWAIT418END CATCH419END420IF @AreRowsToUpdate > 0421BEGIN422 IF @TableName IN(423 SELECT TableName424 FROM QBM_VStartupTables425)426BEGIN427 DELETE @ColumnsAffected428 INSERT INTO @ColumnsAffected(Ident1429)430SELECT431 DISTINCT bu.columnname432FROM QBM_VSchemaColumns cc433JOIN #QBMBufferTransferOneTable bu434 ON cc.COLUMN_NAME = bu.columnname435WHERE436 cc.TABLE_NAME = @TableName437ORDER BY 1438SELECT @ElementCount = @@ROWCOUNT439SELECT @ElementIndex = 1440END441ELSE442BEGIN443 DELETE @ColumnsAffected444 INSERT INTO @ColumnsAffected(Ident1445)446SELECT447 DISTINCT bu.columnname448FROM dialogColumn cc449 WITH(readpast450)451JOIN QBMBufferTransfer bu452 WITH(readpast453)454 ON cc.ColumnName = bu.ColumnName455JOIN DialogTable t456 WITH(readpast457)458 ON cc.UID_DialogTable = t.UID_DialogTable459WHERE460 t.TableName = @TableName AND cc.IsPKMember = 0461ORDER BY 1462SELECT @ElementCount = @@ROWCOUNT463SELECT @ElementIndex = 1464END465WHILE @ElementIndex <= @ElementCount466BEGIN467 SELECT TOP 1 @columnname = bu.Ident1468 FROM @ColumnsAffected bu469 WHERE470 bu.ElementIndex = @ElementIndex471 IF @DebugSwitch > 0472 BEGIN473 print ' updat' + 'e ' + @columnname474 END475 EXEC QBM_PBufferT_ProcessUpdate @modulename,476 @TableName,477 @columnname478 SELECT @ElementIndex += 1479END480END481IF @AreRowsToInsert > 0482BEGIN483 IF @DebugSwitch > 0 AND @DebugLevel = 'T'484 BEGIN485 SELECT486 @SQLCmd = N '487 declare @msg nvarchar(256)488 select top 1 @msg = ''to insert: '' + y.ObjectKeyOfRow489 from ' + @TableName + ' x with (readpast) right outer join #QBMBufferTransferOneTable y on x.XObjectKey = y.ObjectKeyOfRow490 where x.XObjectKey is null491 492 exec QBM_PJournal @msg, @@procid, ''T'', ''T''493 '494 EXEC sp_executesql @SQLCmd495 END496 EXEC QBM_PBufferT_ProcessInsert @modulename,497 @TableName498END499IF @AreRowsToInsert + @AreRowsToUpdate + @AreRowsToDelete > 0 AND @TableName IN(500 SELECT t.TableName501 FROM QBM_VStartupTables t502)503BEGIN504 UPDATE DialogScriptAssembly505 SET isValid = 0,506 XDateUpdated = @Xdate,507 XUserUpdated = @XUser508 WHERE509 isvalid = 1510END511END TRY512BEGIN CATCH513 EXEC QBM_PSessionErrorAdd DEFAULT514 DECLARE @Rethrow varchar(1000515) = dbo.QBM_FGISessionErrorRethrow(516)517RAISERROR(@Rethrow,51818,5191520)521 WITH NOWAIT522END CATCH523endLabel: truncate TABLE #QBMBufferTransferOneTable524RETURN525ENDOpen raw exported source
1 create procedure QBM_PBufferT_ProcessTable ( @ModuleName varchar(3) , @TableName varchar(30) , @PurgeMode bit = 0 ) as begin declare @columnname2 varchar(30) declare @SQLCmd nvarchar(max) declare @CountUpcomingRows int declare @DebugSwitch int = 0 declare @DebugLevel varchar(1) = 'W' declare @msg3 nvarchar(4000) declare @ErrorMessage nvarchar(4000) declare @ErrorSeverity int declare @ErrorState int declare @ObjectsToDelete QBM_YParameterList declare4 @UID_Module varchar(38) declare @GenprocID varchar(38) declare @RowCondition_Table nvarchar(max) declare @AreRowsToUpdate int = 0 declare @AreRowsToInsert5 int = 0 declare @AreRowsToDelete int = 0 declare @ColumnsAffected QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @IsTransportDisabled6 bit declare @XUser nvarchar(64) = object_name(@@procid) declare @Xdate datetime = getutcdate() declare @PredecessorsInclNonlinear varchar(2000) = '' SET7 XACT_ABORT OFF BEGIN TRY select top 1 @PredecessorsInclNonlinear = concat( '(', STRING_AGG( concat('''', left(x.UID_ModuleDef , 3) , '-'''), ',') , ')'8 ) from ( select m.UID_ModuleDef from QBMModuleDef m with (readpast) join QBMModuleDef nl with (readpast) on nl.ModuleInfoXML like concat('%' , '<NonlinearModules>%<Module ModuleID="'9, m.ModuleName, '"%</NonlinearModules>%' ) where nl.UID_ModuleDef = concat(@moduleName, '-Moduledefinition') union select co.UID_ModulePredecessor from10 QBMModuleDependCollection co with (readpast) where co.UID_ModuleFollower = concat(@moduleName, '-Moduledefinition') union select concat(@ModuleName ,11 '-Moduledefinition' ) ) as x select top 1 @RowCondition_Table = isnull(t.TransportWhereClause, '') , @IsTransportDisabled = t.IsTransportDisabled from12 DialogTable t with (readpast) where t.TableName = @TableName if @DebugSwitch > 0 begin print '@RowCondition_Table ' + isnull(@RowCondition_Table, '#'13) print '@IsTransportDisabled ' + isnull(str(@IsTransportDisabled), '#') end select @UID_Module = @ModuleName + '-Moduledefinition' select @GenprocID =14 NEWID() drop table if exists #QBMBufferTransferOneTable create table #QBMBufferTransferOneTable (ColumnName varchar(30) collate database_default ,15 ObjectKeyOfRow varchar(138) collate database_default , ContentShort nvarchar(400) collate database_default , HasContentFull bit default 0 , ContentFull16 nvarchar(max) collate database_default ) if not exists (select top 1 1 from sys.tables t with (readpast) where t.name = @TableName ) begin if @ModuleName17 = 'CCC' begin select @msg = '#LDS#targettable {0} not found|' + @TableName + '|' raiserror (@msg, 18, 1) with nowait end else begin goto endLabel end 18end insert into #QBMBufferTransferOneTable(ColumnName, ObjectKeyOfRow, ContentShort, HasContentFull, ContentFull) select ColumnName, ObjectkeyOfRow, ContentShort19, 0, null from QBMBufferTransfer with (readpast) where TableName = @TableName and ModuleName = @ModuleName and HasContentFull = 0 select @CountUpcomingRows20 = @@ROWCOUNT insert into #QBMBufferTransferOneTable(ColumnName, ObjectKeyOfRow, ContentShort, HasContentFull, ContentFull) select ColumnName, ObjectkeyOfRow21, ContentShort, HasContentFull, ContentFull from QBMBufferTransfer with (readpast) where TableName = @TableName and ModuleName = @ModuleName and HasContentFull22 = 1 select @CountUpcomingRows += @@ROWCOUNT create index #XIE1_QBMBufferTransferOneTable on #QBMBufferTransferOneTable(ObjectKeyOfRow, columnname) include23 (ContentShort) create index #XIE2_QBMBufferTransferOneTable on #QBMBufferTransferOneTable(ColumnName) INCLUDE (ObjectKeyOfRow,ContentShort) if @DebugSwitch24 > 0 begin print '-- umgeladen table ' + @TableName + dbo.QBM_FCVDatetimeToString(GETUTCDATE()) end if exists (select top 1 1 from #QBMBufferTransferOneTable25 bt join QBMLock l with (readpast) on l.ObjectKeyOfRow = bt.ObjectKeyOfRow ) begin delete #QBMBufferTransferOneTable from #QBMBufferTransferOneTable bt26 join DialogTable t with (readpast) on t.TableName = @TableName join DialogColumn c with (readpast) on t.UID_DialogTable = c.UID_DialogTable and c.ColumnName27 = bt.ColumnName join QBMLock l with (readpast) on l.UID_DialogColumn = c.UID_DialogColumn and l.ObjectKeyOfRow = bt.ObjectKeyOfRow delete #QBMBufferTransferOneTable28 from #QBMBufferTransferOneTable bt join QBMLock l with (readpast) on l.UID_DialogColumn is null and l.ObjectKeyOfRow = bt.ObjectKeyOfRow end if @CountUpcomingRows29 > 0 begin select @SQLCmd = N'select top 1 130 from ' + @TableName + ' x with (readpast) join #QBMBufferTransferOneTable y on x.XObjectKey = y.ObjectKeyOfRow'31 if @DebugSwitch > 0 begin print @SQLCmd end begin try exec sp_executesql @sqlcmd select @AreRowsToUpdate = @@ROWCOUNT end try begin catch exec QBM_PSessionErrorAdd32 default, @SQLCmd RAISERROR ('', 18, 1) WITH NOWAIT end catch if @DebugSwitch > 0 begin print '@AreRowsToUpdate: ' + isnull(str(@AreRowsToUpdate), 'null'33) end end if @PurgeMode = 1 and @TableName <> 'QBMNonLinearDepend' and @IsTransportDisabled = 0 begin select @SQLCmd = N'select top 1 134 from '35 + @TableName + ' with (readpast) left outer join #QBMBufferTransferOneTable y on ' + @TableName + '.XObjectKey = y.ObjectKeyOfRow 36 where ' +37 dbo.QBM_FCVStringToIndent( dbo.QBM_FSQTableRowOwnedByModuleNL(@TableName , @Modulename, '' + @TableName + '', @PredecessorsInclNonlinear), 11) + '38 and y.ObjectKeyOfRow is null39 '40 if @DebugSwitch > 0 begin print @SQLCmd end begin try exec sp_executesql @sqlcmd select @AreRowsToDelete = @@ROWCOUNT end try begin catch exec QBM_PSessionErrorAdd41 default, @SQLCmd RAISERROR ('', 18, 1) WITH NOWAIT end catch if @DebugSwitch > 0 begin print '@AreRowsToDelete ' + isnull(str(@AreRowsToDelete), 'null'42) end if @AreRowsToDelete > 0 begin exec QBM_PTriggersInactiveForTable @TableName, 'Delete' end end if @CountUpcomingRows > 0 begin select @SQLCmd43 = N'select top 1 144 from ' + @TableName + ' x with (readpast) right outer join #QBMBufferTransferOneTable y on x.XObjectKey = y.ObjectKeyOfRow45 where x.XObjectKey is null'46 if @DebugSwitch > 0 begin print @SQLCmd end begin try exec sp_executesql @sqlcmd select @AreRowsToInsert = @@ROWCOUNT end try begin catch exec QBM_PSessionErrorAdd47 default, @SQLCmd RAISERROR ('', 18, 1) WITH NOWAIT end catch if @DebugSwitch > 0 begin print '@AreRowsToInsert: ' + isnull(str(@AreRowsToInsert), 'null'48) end if @AreRowsToInsert > 0 begin exec QBM_PTriggersInactiveForTable @TableName, 'Insert' end end if @CountUpcomingRows = 0 and @AreRowsToDelete49 > 0 begin select @SQLCmd = 'Delete ' + @TableName + '50 where XObjectKey in ( select x.XObjectKey51 from ' + @TableName + ' x with (readpast)52 where '53 + dbo.QBM_FCVStringToIndent( dbo.QBM_FSQTableRowOwnedByModuleNL(@TableName , @Modulename, 'x', @PredecessorsInclNonlinear), 11) + ' 54 )55 and not exists (select top 1 1 56 from QBMLock l with (readpast)57 where l.ObjectKeyOfRow = '58 + @TableName + '.XObjectKey59 )60 ' if @RowCondition_Table > ' ' begin select @SQLCmd = @SQLCmd + '61 and ( ' + @RowCondition_Table62 + '63 )64 ' end if @DebugSwitch > 0 begin print @SQLcmd end begin try exec sp_executesql @SQLcmd end try begin catch65 exec QBM_PSessionErrorAdd default, @SQLCmd RAISERROR ('', 18, 1) WITH NOWAIT end catch end if @CountUpcomingRows > 0 and @AreRowsToDelete > 0 begin66 select @SQLCmd = ' select distinct ' + @TableName + '.XObjectKey67 from ' + @TableName + ' with (readpast) left outer join #QBMBufferTransferOneTable y on '68 + @TableName + '.XObjectKey = y.ObjectKeyOfRow 69 where ' + dbo.QBM_FCVStringToIndent( dbo.QBM_FSQTableRowOwnedByModuleNL(@TableName , @Modulename70, '' + @TableName + '', @PredecessorsInclNonlinear), 11) + '71 and y.ObjectKeyOfRow is null72 and not exists (select top 1 1 73 from QBMLock l with (readpast)74 where l.ObjectKeyOfRow = '75 + @TableName + '.XObjectKey76 )77 ' if @RowCondition_Table > ' ' begin select @SQLCmd = @SQLCmd + '78 and ( ' + @RowCondition_Table79 + '80 )81 ' end if @DebugSwitch > 0 begin print @SQLcmd end BEGIN TRY delete @ObjectsToDelete insert into @ObjectsToDelete(Parameter182) exec sp_executesql @SQLcmd delete @ObjectsToDelete where Parameter1 in (select ObjectKeyOfRow from QBMLock with (readpast) ) select @SQLCmd =83 'Delete ' + @TableName + '84 from ' + @TableName + ' with (readpast) left outer join #QBMBufferTransferOneTable y on ' + @TableName + '.XObjectKey = y.ObjectKeyOfRow 85 where '86 + dbo.QBM_FCVStringToIndent( dbo.QBM_FSQTableRowOwnedByModuleNL(@TableName , @Modulename, '' + @TableName + '', @PredecessorsInclNonlinear), 11) + '87 and y.ObjectKeyOfRow is null88 and not exists (select top 1 1 89 from QBMLock l with (readpast)90 where l.ObjectKeyOfRow = '91 + @TableName + '.XObjectKey92 )93 ' if @RowCondition_Table > ' ' begin select @SQLCmd = @SQLCmd + '94 and ( ' + @RowCondition_Table95 + '96 )97 ' end if @DebugSwitch > 0 begin print @SQLcmd end exec sp_executesql @SQLcmd END TRY BEGIN CATCH98 exec QBM_PSessionErrorAdd default, @SQLCmd RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end if @AreRowsToUpdate > 0 begin if @TableName in (select 99TableName from QBM_VStartupTables) begin delete @ColumnsAffected insert into @ColumnsAffected(Ident1) select distinct bu.columnname from QBM_VSchemaColumns100 cc join #QBMBufferTransferOneTable bu on cc.COLUMN_NAME = bu.columnname where cc.TABLE_NAME = @TableName order by 1 select @ElementCount = @@ROWCOUNT 101select @ElementIndex = 1 end else begin delete @ColumnsAffected insert into @ColumnsAffected(Ident1) select distinct bu.columnname from dialogColumn 102cc with (readpast) join QBMBufferTransfer bu with (readpast) on cc.ColumnName = bu.ColumnName join DialogTable t with (readpast) on cc.UID_DialogTable 103= t.UID_DialogTable where t.TableName = @TableName and cc.IsPKMember = 0 order by 1 select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 end while104 @ElementIndex <= @ElementCount begin select top 1 @columnname = bu.Ident1 from @ColumnsAffected bu where bu.ElementIndex = @ElementIndex if @DebugSwitch105 > 0 begin print ' updat'+'e ' + @columnname end exec QBM_PBufferT_ProcessUpdate @modulename, @TableName , @columnname select @ElementIndex += 1 end106 end if @AreRowsToInsert > 0 begin if @DebugSwitch > 0 and @DebugLevel = 'T' begin select @SQLCmd = N'107 declare @msg nvarchar(256)108 select top 1 @msg = ''to insert: '' + y.ObjectKeyOfRow109 from '110 + @TableName + ' x with (readpast) right outer join #QBMBufferTransferOneTable y on x.XObjectKey = y.ObjectKeyOfRow111 where x.XObjectKey is null112 113 exec QBM_PJournal @msg, @@procid, ''T'', ''T''114 '115 exec sp_executesql @SQLCmd end exec QBM_PBufferT_ProcessInsert @modulename, @TableName end if @AreRowsToInsert + @AreRowsToUpdate + @AreRowsToDelete 116> 0 and @TableName in (select t.TableName from QBM_VStartupTables t ) begin update DialogScriptAssembly set isValid = 0 , XDateUpdated = @Xdate , XUserUpdated117 = @XUser where isvalid = 1 end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()118 RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: truncate table #QBMBufferTransferOneTable return end 119