dbo.QBM_PDeleteBulk

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 nvarchar
3(max) = null declare @Message nvarchar(max) declare @CountItems int declare @ColLen_1 int declare @ColLen_2 int declare @CountTotal int declare @uid_dialogTable
4 varchar(38) declare @DebugSwitch int = 0 declare @Durchlaufzaehler int = 0 declare @SQLDefault nvarchar(max) , @SQLRecompile nvarchar(max) SET XACT_ABORT
5 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, KeyColumn2
10, KeyLen2) where t.TableName = @TableName if @KeyColumn_intern_1 > ' ' begin if @DebugSwitch > 0 begin print 'known table ' + @TableName + ' (' + @KeyColumn_intern_1
11 + ')' end goto readyToExe end select top 1 @uid_dialogTable = uid_dialogtable from DialogTable with (readpast) where TableName = @TableName select @KeyColumn_intern_2
12 = N'' select @ColLen_2 = 0 if exists (select top 1 1 from QBM_VSchemaColumns where table_name = @TableName and column_name = 'XObjectKey' ) begin select
13 @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 @Message
15 = '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 and
17 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_2
18 = 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_YSingleGUID
21	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#)
29
30	if @@rowcount > 0
31	 begin
32		delete %TableName%
33			where %KeyColumn1% in (select UID_SingleGuid from @d)  
34		option (maxdop 1 #recompile#)
35	 end
36		'
37 end if @CountKeyColumns = 1 and @ColLen_1 = 138 begin select @SQLcmd = N'
38	declare @d QBM_YSingleObjectKey
39	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#)
47
48	if @@rowcount > 0
49	 begin
50		delete %TableName%
51			where %KeyColumn1% in (select SingleObjectKey from @d)  
52		option (maxdop 1 #recompile#)
53	 end
54		'
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#)
67
68	if @@rowcount > 0
69	 begin
70		delete %TableName%
71			where %KeyColumn1% in (select keyColumn1 from @d)  
72		option (maxdop 1 #recompile#)
73	 end
74		'
75 end if @CountKeyColumns = 2 and @ColLen_1 = 38 and @ColLen_2 = 38  begin  select @SQLcmd = N'
76	declare @d QBM_YMNTable
77	insert into @d (UID_Element1, UID_Element2)
78	select top %n% %KeyColumn1%, %KeyColumn2%
79		from %TableName% with (readpast)
80		where (