Back to OIM Explorer

dbo.QBM_PDeleteBulk

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 6.936 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_FGISessionErrorRethrow source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

1CREATE PROCEDURE QBM_PDeleteBulk(2  @TableName varchar(30),3  @whereclause nvarchar(max),4  @RowLimit int = 500,5  @MaxLimit int = 1000000006)7AS8BEGIN9  DECLARE @CountKeyColumns int10  DECLARE @KeyColumn_intern_1 varchar(30) = NULL11  DECLARE @KeyColumn_intern_2 varchar(30)12  DECLARE @SQLCmd nvarchar(max) = NULL13  DECLARE @Message nvarchar(max)14  DECLARE @CountItems int15  DECLARE @ColLen_1 int16  DECLARE @ColLen_2 int17  DECLARE @CountTotal int18  DECLARE @uid_dialogTable varchar(38)19  DECLARE @DebugSwitch int = 020  DECLARE @Durchlaufzaehler int = 021  DECLARE @SQLDefault nvarchar(max),22  @SQLRecompile nvarchar(max)23  SET XACT_ABORT OFF24  BEGIN TRY25    SELECT26      TOP 1 @CountKeyColumns = t.CountKeyColumns,27      @KeyColumn_intern_1 = t.KeyColumn1,28      @KeyColumn_intern_2 = t.KeyColumn2,29      @ColLen_1 = t.KeyLen1,30      @ColLen_2 = t.KeyLen231    FROM(32    VALUES('DialogWatchOperation', 1, 'UID_DialogWatchOperation', 38, '', 0),33    ('DialogJournal', 1, 'UID_DialogJournal', 38, '', 0),34    ('DialogProcessChain', 1, 'UID_Tree', 38, '', 0),35    ('DialogProcessSubstitute', 2, 'GenProcIDOrigin', 38, 'GenProcIDNew', 38),36    ('DialogProcess', 1, 'GenProcID', 38, '', 0),37    ('JobQueueStats', 1, 'UID_JobQueueStats', 38, '', 0),38    ('JobHistory', 1, 'UID_JobHistory', 38, '', 0),39    ('QBMDBQueueCurrent', 1, 'UID_DialogDBQueue', 38, '', 0),40    ('DialogDBQueue', 1, 'UID_DialogDBQueue', 38, '', 0)) AS t(TableName,41    CountKeyColumns,42    KeyColumn1,43    KeyLen1,44    KeyColumn2,45    KeyLen2)46    WHERE47      t.TableName = @TableName48    IF @KeyColumn_intern_1 > ' '49    BEGIN50      IF @DebugSwitch > 051      BEGIN52        print 'known table ' + @TableName + ' (' + @KeyColumn_intern_1 + ')'53      END54      GOTO readyToExe55    END56    SELECT TOP 1 @uid_dialogTable = uid_dialogtable57    FROM DialogTable58      WITH(readpast)59    WHERE60      TableName = @TableName61    SELECT @KeyColumn_intern_2 = N ''62    SELECT @ColLen_2 = 063    IF EXISTS(64      SELECT TOP 1 165      FROM QBM_VSchemaColumns66      WHERE67        table_name = @TableName AND column_name = 'XObjectKey')68    BEGIN69      SELECT @KeyColumn_intern_1 = 'XObjectKey'70      SELECT @CountKeyColumns = 171      SELECT @ColLen_1 = 13872      GOTO geklaert73    END74    SELECT TOP 1 @CountKeyColumns = count(*)75    FROM DialogColumn c76      WITH(readpast)77    WHERE78      c.UID_DialogTable = @uid_dialogTable AND c.IsPKMember = 179    IF @CountKeyColumns NOT IN(1,80    2)81    BEGIN82      SELECT83        @Message = 'invalid count of key columns for QBM_PDeleteBulk in table ' + @TableName84      RAISERROR(@Message,85      18,86      1)87        WITH nowait88    END89    SELECT @ColLen_1 = NULL90    SELECT91      TOP 1 @KeyColumn_intern_1 = c.ColumnName,92      @ColLen_1 = c.SchemaDataLen93    FROM DialogColumn c94      WITH(readpast)95    WHERE96      c.UID_DialogTable = @uid_dialogTable AND c.IsPKMember = 197    ORDER BY c.ColumnName ASC98    IF @CountKeyColumns = 299    BEGIN100      SELECT @ColLen_2 = NULL101      SELECT102        TOP 1 @KeyColumn_intern_2 = c.ColumnName,103        @ColLen_2 = c.SchemaDataLen104      FROM DialogColumn c105        WITH(readpast)106      WHERE107        c.UID_DialogTable = @uid_dialogTable AND c.IsPKMember = 1108      ORDER BY c.ColumnName DESC109    END110    geklaert: readytoexe:111    IF @CountKeyColumns = 1 AND @ColLen_1 = 38112    BEGIN113      SELECT114        @SQLcmd = N '115	declare @d QBM_YSingleGUID116	insert into @d (UID_SingleGuid)117	select top %n% %KeyColumn1%118		from %TableName% with (readpast)119		where ( 120%whereclause%121				)122		--order by %KeyColumn1%123		option (maxdop 1 #recompile#)124125	if @@rowcount > 0126	 begin127		delete %TableName%128			where %KeyColumn1% in (select UID_SingleGuid from @d)  129		option (maxdop 1 #recompile#)130	 end131		'132    END133    IF @CountKeyColumns = 1 AND @ColLen_1 = 138134    BEGIN135      SELECT136        @SQLcmd = N '137	declare @d QBM_YSingleObjectKey138	insert into @d (SingleObjectKey)139	select top %n% %KeyColumn1%140		from %TableName% with (readpast)141		where ( 142%whereclause%143				)144		--order by %KeyColumn1%145		option (maxdop 1 #recompile#)146147	if @@rowcount > 0148	 begin149		delete %TableName%150			where %KeyColumn1% in (select SingleObjectKey from @d)  151		option (maxdop 1 #recompile#)152	 end153		'154    END155    IF @CountKeyColumns = 1 AND @SQLCmd IS NULL156    BEGIN157      SELECT158        @SQLcmd = N '159	declare @d table (KeyColumn1 varchar(%ColLen_1%) collate database_default 160						--primary key (KeyColumn1) 161					)162	insert into @d (KeyColumn1)163	select top %n% %KeyColumn1%164		from %TableName% with (readpast)165		where ( 166%whereclause%167				)168		--order by %KeyColumn1%169		option (maxdop 1 #recompile#)170171	if @@rowcount > 0172	 begin173		delete %TableName%174			where %KeyColumn1% in (select keyColumn1 from @d)  175		option (maxdop 1 #recompile#)176	 end177		'178    END179    IF @CountKeyColumns = 2 AND @ColLen_1 = 38 AND @ColLen_2 = 38180    BEGIN181      SELECT182        @SQLcmd = N '183	declare @d QBM_YMNTable184	insert into @d (UID_Element1, UID_Element2)185	select top %n% %KeyColumn1%, %KeyColumn2%186		from %TableName% with (readpast)187		where ( 188%whereclause%189				)190		--order by %KeyColumn1%, %KeyColumn2%191		option (maxdop 1 #recompile#)192193	if @@rowcount > 0194	 begin195		delete %TableName%196			from %TableName% t join @d d on d.UID_Element1 = t.%KeyColumn1%197										and d.UID_Element2 = t.%KeyColumn2%198							199		option (maxdop 1 #recompile#)200	  end  '201    END202    IF @CountKeyColumns = 2 AND @SQLCmd IS NULL203    BEGIN204      SELECT205        @SQLcmd = N '206	declare @d table (KeyColumn1 varchar(%ColLen_1%) collate database_default, 207						KeyColumn2 varchar(%ColLen_2%) collate database_default 208						--primary key (KeyColumn1, KeyColumn2) 209					)210	insert into @d (KeyColumn1, KeyColumn2)211	select top %n% %KeyColumn1%, %KeyColumn2%212		from %TableName% with (readpast)213		where ( 214%whereclause%215				)216		--order by %KeyColumn1%, %KeyColumn2%217		option (maxdop 1 #recompile#)218219	if @@rowcount > 0220	 begin221		delete %TableName%222			from %TableName% t join @d d on d.KeyColumn1 = t.%KeyColumn1%223										and d.KeyColumn2 = t.%KeyColumn2%224							225		option (maxdop 1 #recompile#)226	  end  '227    END228    SELECT229      @SQLcmd = replace(replace(replace(replace(replace(replace(replace(@SQLcmd, N '%TableName%', @TableName),230      N '%KeyColumn1%', @KeyColumn_intern_1), N '%KeyColumn2%', @KeyColumn_intern_2), N '%n%', str(@RowLimit)),231      N '%whereclause%', @whereclause), N '%ColLen_1%', str(@ColLen_1)),232      N '%ColLen_2%',233      str(@ColLen_2))234    IF @DebugSwitch > 0235    BEGIN236      print isnull(@SQLcmd,237      '<kein sql>')238    END239    SELECT240      @SQLDefault = REPLACE(@SQLcmd,241      '#recompile#',242      N '')243    SELECT244      @SQLRecompile = REPLACE(@SQLcmd,245      '#recompile#',246      N ' , recompile')247    SELECT @CountTotal = 0248    SELECT @CountItems = 1249    WHILE(@CountItems > 0 AND @CountTotal < @MaxLimit)250    BEGIN251      IF @Durchlaufzaehler % 10 = 0252      BEGIN253        IF @DebugSwitch > 0254        BEGIN255          print @SQLRecompile256        END257        EXEC sp_executesql @SQLRecompile258      END259      ELSE260      BEGIN261        IF @DebugSwitch > 0262        BEGIN263          print @SQLDefault264        END265        EXEC sp_executesql @SQLDefault266      END267      SELECT @CountItems = @@ROWCOUNT268      SELECT @CountTotal += @CountItems269      IF @DebugSwitch > 0270      BEGIN271        print '@CountItems' + str(@CountItems) print '@CountTotal' + str(@CountTotal)272      END273      IF @CountItems < @RowLimit274      BEGIN275        SELECT @CountItems = 0276      END277      SELECT @Durchlaufzaehler += 1278    END279  END TRY280  BEGIN CATCH281    EXEC QBM_PSessionErrorAdd DEFAULT282    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()283    RAISERROR(@Rethrow,284    18,285    1)286      WITH NOWAIT287  END CATCH288  ende:289  RETURN(@CountTotal)290END

Open raw exported source

1     create   procedure QBM_PDeleteBulk (@TableName varchar(30) , @whereclause nvarchar(max) , @RowLimit int = 500 , @MaxLimit int = 100000000  2) as begin declare @CountKeyColumns int  declare @KeyColumn_intern_1 varchar(30) = null declare @KeyColumn_intern_2 varchar(30) declare @SQLCmd nvarchar3(max) = null declare @Message nvarchar(max) declare @CountItems int declare @ColLen_1 int declare @ColLen_2 int declare @CountTotal int declare @uid_dialogTable4 varchar(38) declare @DebugSwitch int = 0 declare @Durchlaufzaehler int = 0 declare @SQLDefault nvarchar(max) , @SQLRecompile nvarchar(max) SET XACT_ABORT5 OFF BEGIN TRY select top 1 @CountKeyColumns = t.CountKeyColumns , @KeyColumn_intern_1 = t.KeyColumn1 , @KeyColumn_intern_2 = t.KeyColumn2 , @ColLen_1 6= t.KeyLen1 , @ColLen_2 = t.KeyLen2 from ( values( 'DialogWatchOperation', 1, 'UID_DialogWatchOperation', 38, '', 0) , ('DialogJournal', 1, 'UID_DialogJournal'7, 38, '', 0) , ('DialogProcessChain', 1, 'UID_Tree', 38, '', 0) , ('DialogProcessSubstitute', 2, 'GenProcIDOrigin', 38, 'GenProcIDNew', 38) , ('DialogProcess'8, 1, 'GenProcID', 38, '', 0) , ('JobQueueStats', 1, 'UID_JobQueueStats', 38, '', 0) , ('JobHistory', 1, 'UID_JobHistory', 38, '', 0) , ('QBMDBQueueCurrent'9, 1, 'UID_DialogDBQueue', 38, '', 0) , ('DialogDBQueue', 1, 'UID_DialogDBQueue', 38, '', 0) ) as t (TableName, CountKeyColumns, KeyColumn1,KeyLen1, KeyColumn210, KeyLen2) where t.TableName = @TableName if @KeyColumn_intern_1 > ' ' begin if @DebugSwitch > 0 begin print 'known table ' + @TableName + ' (' + @KeyColumn_intern_111 + ')' end goto readyToExe end select top 1 @uid_dialogTable = uid_dialogtable from DialogTable with (readpast) where TableName = @TableName select @KeyColumn_intern_212 = N'' select @ColLen_2 = 0 if exists (select top 1 1 from QBM_VSchemaColumns where table_name = @TableName and column_name = 'XObjectKey' ) begin select13 @KeyColumn_intern_1 = 'XObjectKey' select @CountKeyColumns = 1 select @ColLen_1 = 138 goto geklaert end select top 1 @CountKeyColumns = count(*) from 14DialogColumn c with (readpast) where c.UID_DialogTable = @uid_dialogTable and c.IsPKMember = 1  if @CountKeyColumns not in ( 1, 2) begin select @Message15 = 'invalid count of key columns for QBM_PDeleteBulk in table ' + @TableName raiserror(@Message, 18, 1) with nowait end select @ColLen_1 = null select 16top 1 @KeyColumn_intern_1 = c.ColumnName , @ColLen_1 = c.SchemaDataLen from DialogColumn c with (readpast) where c.UID_DialogTable = @uid_dialogTable and17 c.IsPKMember = 1 order by c.ColumnName asc if @CountKeyColumns = 2 begin select @ColLen_2 = null select top 1 @KeyColumn_intern_2 = c.ColumnName , @ColLen_218 = c.SchemaDataLen from DialogColumn c with (readpast) where c.UID_DialogTable = @uid_dialogTable and c.IsPKMember = 1 order by c.ColumnName desc end geklaert:19    readytoexe: if @CountKeyColumns = 1 and @ColLen_1 = 38 begin select @SQLcmd = N'20	declare @d QBM_YSingleGUID21	insert into @d (UID_SingleGuid)22	select top %n% %KeyColumn1%23		from %TableName% with (readpast)24		where ( 25%whereclause%26				)27		--order by %KeyColumn1%28		option (maxdop 1 #recompile#)2930	if @@rowcount > 031	 begin32		delete %TableName%33			where %KeyColumn1% in (select UID_SingleGuid from @d)  34		option (maxdop 1 #recompile#)35	 end36		'37 end if @CountKeyColumns = 1 and @ColLen_1 = 138 begin select @SQLcmd = N'38	declare @d QBM_YSingleObjectKey39	insert into @d (SingleObjectKey)40	select top %n% %KeyColumn1%41		from %TableName% with (readpast)42		where ( 43%whereclause%44				)45		--order by %KeyColumn1%46		option (maxdop 1 #recompile#)4748	if @@rowcount > 049	 begin50		delete %TableName%51			where %KeyColumn1% in (select SingleObjectKey from @d)  52		option (maxdop 1 #recompile#)53	 end54		'55 end  if @CountKeyColumns = 1 and @SQLCmd is null   begin select @SQLcmd = N'56	declare @d table (KeyColumn1 varchar(%ColLen_1%) collate database_default 57						--primary key (KeyColumn1) 58					)59	insert into @d (KeyColumn1)60	select top %n% %KeyColumn1%61		from %TableName% with (readpast)62		where ( 63%whereclause%64				)65		--order by %KeyColumn1%66		option (maxdop 1 #recompile#)6768	if @@rowcount > 069	 begin70		delete %TableName%71			where %KeyColumn1% in (select keyColumn1 from @d)  72		option (maxdop 1 #recompile#)73	 end74		'75 end if @CountKeyColumns = 2 and @ColLen_1 = 38 and @ColLen_2 = 38  begin  select @SQLcmd = N'76	declare @d QBM_YMNTable77	insert into @d (UID_Element1, UID_Element2)78	select top %n% %KeyColumn1%, %KeyColumn2%79		from %TableName% with (readpast)80		where ( 81%whereclause%82				)83		--order by %KeyColumn1%, %KeyColumn2%84		option (maxdop 1 #recompile#)8586	if @@rowcount > 087	 begin88		delete %TableName%89			from %TableName% t join @d d on d.UID_Element1 = t.%KeyColumn1%90										and d.UID_Element2 = t.%KeyColumn2%91							92		option (maxdop 1 #recompile#)93	  end  '94 end  if @CountKeyColumns = 2 and @SQLCmd is null begin  select @SQLcmd = N'95	declare @d table (KeyColumn1 varchar(%ColLen_1%) collate database_default, 96						KeyColumn2 varchar(%ColLen_2%) collate database_default 97						--primary key (KeyColumn1, KeyColumn2) 98					)99	insert into @d (KeyColumn1, KeyColumn2)100	select top %n% %KeyColumn1%, %KeyColumn2%101		from %TableName% with (readpast)102		where ( 103%whereclause%104				)105		--order by %KeyColumn1%, %KeyColumn2%106		option (maxdop 1 #recompile#)107108	if @@rowcount > 0109	 begin110		delete %TableName%111			from %TableName% t join @d d on d.KeyColumn1 = t.%KeyColumn1%112										and d.KeyColumn2 = t.%KeyColumn2%113							114		option (maxdop 1 #recompile#)115	  end  '116 end select @SQLcmd = replace(replace(replace(replace(replace(replace(replace( @SQLcmd,N'%TableName%', @TableName ), N'%KeyColumn1%', @KeyColumn_intern_1117), N'%KeyColumn2%', @KeyColumn_intern_2), N'%n%', str(@RowLimit)), N'%whereclause%', @whereclause), N'%ColLen_1%', str(@ColLen_1)), N'%ColLen_2%', str(@ColLen_2118)) if @DebugSwitch > 0 begin print isnull(@SQLcmd, '<kein sql>') end select @SQLDefault = REPLACE(@SQLcmd, '#recompile#' , N'') select @SQLRecompile = 119REPLACE(@SQLcmd, '#recompile#' , N' , recompile') select @CountTotal = 0 select @CountItems = 1 while (@CountItems > 0 and @CountTotal < @MaxLimit ) begin120  if @Durchlaufzaehler % 10 = 0  begin if @DebugSwitch > 0 begin print @SQLRecompile end exec sp_executesql @SQLRecompile end else begin if @DebugSwitch121 > 0 begin print @SQLDefault end exec sp_executesql @SQLDefault end select @CountItems = @@ROWCOUNT select @CountTotal += @CountItems  if @DebugSwitch 122> 0 begin print '@CountItems' + str(@CountItems) print '@CountTotal' + str(@CountTotal) end  if @CountItems < @RowLimit begin select @CountItems = 0 end123 select @Durchlaufzaehler += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow124() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH ende: return (@CountTotal) end 125