dbo.ATT_PHelperAttestationPolicy

1create   procedure ATT_PHelperAttestationPolicy ( @UID_AttestationPolicy varchar(38) , @WhereClause nvarchar(max) , @TableName varchar(30) ) AS 
2begin declare @SQLcmd nvarchar(max) declare @Sollzustand QBM_YParameterList declare @Einzufuegen QBM_YParameterList    declare @ZuLoeschen QBM_YParameterList
3  declare @WhereClauseJob nvarchar(max) declare @GenProcID varchar(38) = dbo.QBM_FGISessionContext('') declare @DebugSwitch int = 0 declare @DebugStart
4 datetime = getutcdate() declare @HexPatterns QBM_YCursorBuffer declare @CountChunks int declare @ElementIndex int declare @AnzahlElemente int declare 
5@HexPattern varchar(200)   declare @isInactive bit declare @IsShowElementsInvolved bit SET XACT_ABORT OFF BEGIN TRY set nocount on exec QBM_PTransactionLevelCheck
6 0, @@procid select top 1 @isInactive = p.IsInActive , @IsShowElementsInvolved = p.IsShowElementsInvolved from AttestationPolicy p where uid_AttestationPolicy
7 = @uid_AttestationPolicy if @isInactive = 1 or @IsShowElementsInvolved = 0 begin if @DebugSwitch > 0 begin print 'sofortausstieg wegen inaktiv' end goto
8 DeltaBestimmen end select @SQLcmd = concat('select XObjectKey
9		from ' , @TableName , N'
10		where ' , @WhereClause ) insert into @Sollzustand(Parameter1
11) exec sp_executeSQL @SQLCmd if @DebugSwitch > 0 begin print 'Soll befüllen' + str(datediff(ss, @DebugStart, getutcdate())) select COUNT(*) as AnzahlSoll
12 from @Sollzustand select @DebugStart = GETUTCDATE() end DeltaBestimmen: insert into @ZuLoeschen(Parameter1) select h.UID_HelperAttestationPolicy from 
13HelperAttestationPolicy h left outer join @Sollzustand s on h.UID_AttestationPolicy = @UID_AttestationPolicy and h.ObjectKeyBase = s.Parameter1 where h.UID_AttestationPolicy
14 = @UID_AttestationPolicy and s.Parameter1 is null if @DebugSwitch > 0 begin print 'zu löschen befüllen' + str(datediff(ss, @DebugStart, getutcdate()))
15 select COUNT(*) as AnzahlLoeschen from @ZuLoeschen select @DebugStart = GETUTCDATE() end insert into @Einzufuegen(Parameter1, Parameter2, Parameter3) 
16select dbo.QBM_FCVStringToGUIDMAll(null, @UID_AttestationPolicy, s.Parameter1 ) as UID_HelperAttestationPolicy , s.Parameter1  , dbo.QBM_FCVElementToObjectKey1
17('HelperAttestationPolicy', 'UID_HelperAttestationPolicy', dbo.QBM_FCVStringToGUIDMAll(null, @UID_AttestationPolicy, s.Parameter1 ) ) as XObjectkey from
18 @Sollzustand s left outer join HelperAttestationPolicy h on h.UID_AttestationPolicy = @UID_AttestationPolicy and s.Parameter1 = h.ObjectKeyBase where 
19h.UID_HelperAttestationPolicy is null if @DebugSwitch > 0 begin print 'einzufügen befüllen' + str(datediff(ss, @DebugStart, getutcdate())) select COUNT
20(*) as AnzahlEinzufuegen from @Einzufuegen select @DebugStart = GETUTCDATE() end  select @AnzahlElemente = COUNT(*) from @ZuLoeschen if @AnzahlElemente
21 > 0 begin delete @HexPatterns insert into @HexPatterns (Ident1) select pat.HexPattern from dbo.QBM_FTHexPattern(case when @AnzahlElemente / 50000 < 16
22 then 1 when @AnzahlElemente / 50000 < 256 then 2 when @AnzahlElemente / 50000 < 4096 then 3 else 4 end) as pat select @CountChunks = @@ROWCOUNT select
23 @ElementIndex = 1 while @ElementIndex <= @CountChunks begin select top 1 @HexPattern = p.Ident1 from @HexPatterns p where p.ElementIndex = @ElementIndex
24 if @DebugSwitch > 0 begin print @HexPattern end delete HelperAttestationPolicy from HelperAttestationPolicy h join @ZuLoeschen l on h.UID_HelperAttestationPolicy
25 = l.Parameter1  where l.Parameter1 like @HexPattern select @ElementIndex += 1 end end  if @DebugSwitch > 0 begin print 'gelöscht ' + str(datediff(ss, 
26@DebugStart, getutcdate())) select @DebugStart = GETUTCDATE() end insert into HelperAttestationPolicy( ObjectKeyBase, UID_AttestationPolicy , UID_HelperAttestationPolicy
27, XObjectKey ) select e.Parameter2 as ObjectKeyBase, @UID_AttestationPolicy , e.Parameter1 as UID_HelperAttestationPolicy, e.Parameter3 as XObjectkey from
28 @Einzufuegen e if @DebugSwitch > 0 begin print 'eingefügt ' + str(datediff(ss, @DebugStart, getutcdate())) select @DebugStart = GETUTCDATE() end  select
29 @AnzahlElemente = COUNT(*) from @Einzufuegen if @AnzahlElemente > 0 begin delete @HexPatterns insert into @HexPatterns (Ident1) select pat.HexPattern 
30from dbo.QBM_FTHexPattern(case when @AnzahlElemente / 4000 < 16 then 1 when @AnzahlElemente / 4000 < 256 then 2 when @AnzahlElemente / 4000 < 4096 then
31 3 else 4 end) as pat select @CountChunks = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @CountChunks begin select top 1 @HexPattern = p.Ident1
32 from @HexPatterns p where p.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print @HexPattern end exec ATT_P59CCD84DFA68B81E1B20B65_ @Einzufuegen
33 , @HexPattern , @GenProcID select @ElementIndex += 1 end end  if @DebugSwitch > 0 begin print 'jobs erzeugen' + str(datediff(ss, @DebugStart, getutcdate
34())) select @DebugStart = GETUTCDATE() end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow
35() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH  ende: return end 
36