dbo.ATT_PAttestationCaseCreate

1 create   procedure ATT_PAttestationCaseCreate (  @UID_AttestationRun varchar(38) , @UID_AttestationPolicy varchar(38) , @TableName varchar(30) 
2, @WhereClause nvarchar(max) ) as begin  declare @start datetime = getutcdate() declare @AnzahlElemente int declare @SQLCmd nvarchar(max) declare @Xdate
3 datetime = getutcdate() declare @XUser nvarchar(64) = dbo.QBM_FGISessionContext('XUser') declare @UID_QERWorkingMethod varchar(38) declare @UID_PWODecisionMethod
4 varchar(38) DECLARE @Message nvarchar(1024) declare @DebugSwitch int = 0 if @DebugSwitch > 0 begin declare @CountAt int = dbo.qbm_fgiTableCountall('Attestationcase'
5) declare @CountJQ int = dbo.qbm_fgiTableCountall('Jobqueue') declare @CountPWO int = dbo.qbm_fgiTableCountall(@TableName) print '@CountAt  ' + str(@CountAt
6) print '@CountJQ  ' + str(@CountJQ) print '@CountPWO ' + str(@CountPWO) end select top 1 @UID_PWODecisionMethod = p.UID_PWODecisionMethod from AttestationPolicy
7 p where p.UID_AttestationPolicy = @UID_AttestationPolicy exec QER_PGIQERWorkingMethod @UID_PWODecisionMethod, ''  , @UID_QERWorkingMethod output declare
8 @Zwischenlager QBM_YParameterList    declare @Rohware QBM_YParameterList  declare @HexPatterns QBM_YCursorBuffer declare @CountChunks int declare @CountChunksWithData
9 int = 0 declare @ChunkWithData int declare @ElementIndex int declare @HexPattern varchar(200) SET XACT_ABORT OFF BEGIN TRY set nocount on  if object_id
10('tempdb..#JobQueue') is null begin  Create Table #JobQueue( UID_Job varchar(38) collate database_default NOT NULL , UID_JobSameServer varchar(38) collate
11 database_default NULL , UID_Tree varchar(38) collate database_default NOT NULL , Queue nvarchar(128) collate database_default NOT NULL , ComponentClass
12 nvarchar(200) collate database_default NULL , ExecutionType nvarchar(16) collate database_default NULL , ComponentAssembly nvarchar(255) collate database_default
13 NULL , TaskName nvarchar(64) collate database_default NOT NULL , ParamIN nvarchar(max) collate database_default NULL , Ready2EXE nvarchar(32) collate 
14database_default NOT NULL , StartAt datetime NULL , XDateInserted datetime NULL , XDateUpdated datetime NULL , XUserInserted nvarchar(64) collate database_default
15 NULL , XUserUpdated nvarchar(64) collate database_default NULL , Retries int default 0 NULL , Priority int default 0 NULL , DeferOnError bit default 0
16 NULL , MinutesToDefer int default 0 NULL , UID_JobError varchar(38) collate database_default NULL , UID_JobSuccess varchar(38) collate database_default
17 NULL , IgnoreErrors bit default 0 NULL , MaxInstance int default 0 NULL , JobChainName nvarchar(255) collate database_default NOT NULL  , IsSplitOnly 
18bit default 0 NULL , WasError bit default 0 NULL , ErrorNotify bit default 0 NULL          , IsRootJob bit default 0 NULL , GenProcID varchar(38) collate
19 database_default NOT NULL  , IsToFreezeOnError bit default 0 NULL   , BasisObjectKey varchar(138) collate database_default NULL      , primary key nonCLUSTERED
20 (UID_Job) , index #JobQueue(ComponentClass,TaskName,JobChainName) INCLUDE (UID_Job) ) end else begin truncate table #JobQueue end if object_id('tempdb..#QBMElementAffectedByJob'
21) is null begin Create Table #QBMElementAffectedByJob( UID_Job varchar(38) collate database_default NOT NULL , ObjectKeyAffected varchar(138) collate database_default
22 NOT NULL , index #QBMElementAffectedByJob (UID_Job) ) end else begin truncate table #QBMElementAffectedByJob end  select @SQLCmd = CONCAT( 'select XObjectkey 
23							from '
24 , @TableName , '
25							where ' , @WhereClause ) insert into @Rohware(Parameter1) exec sp_executesql @SQLCmd if @DebugSwitch > 0 begin select @Message
26 = 'Zeit für Rohware       ' + STR(DATEDIFF(ss, @start, GETUTCDATE())) print @message end insert into @Zwischenlager (Parameter1, Parameter2, Parameter3
27) select dbo.QBM_FCVElementToObjectKey1('AttestationCase', 'UID_AttestationCase', x.UID_AttestationCase) , x.UID_AttestationCase , x.ObjectKeyBase from
28 ( select NEWID() as UID_AttestationCase, r.Parameter1 as ObjectKeyBase from @Rohware r ) x if @DebugSwitch > 0 begin select @Message = 'Zeit für Zwischenlager '
29 + STR(DATEDIFF(ss, @start, GETUTCDATE())) print @message end  select @AnzahlElemente = COUNT(*) from @Zwischenlager insert into @HexPatterns (Ident1) 
30select pat.HexPattern from dbo.QBM_FTHexPattern(case when @AnzahlElemente / 800 < 16 then 1 when @AnzahlElemente / 800 < 256 then 2 when @AnzahlElemente
31 / 800 < 4096 then 3 else 4 end) as pat select @CountChunks = @@ROWCOUNT select @ElementIndex = 1 if @DebugSwitch > 0 begin select @Message = CONCAT('Count Rohware '
32, STR(count(*))) from @Rohware print @message  end while @ElementIndex <= @CountChunks begin select top 1 @HexPattern = p.Ident1 from @HexPatterns p where
33 p.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print @HexPattern end exec ATT_PAttestationCaseCreate_i @Zwischenlager , @UID_AttestationRun 
34, @UID_AttestationPolicy , @UID_QERWorkingMethod , @HexPattern , @Xdate , @XUser , @ChunkWithData output select @CountChunksWithData += @ChunkWithData 
35select @ElementIndex += 1 end   update AttestationRun set CountChunksUnderConstruction += @CountChunksWithData   , XDateUpdated = @Xdate , XUserUpdated
36 = @XUser from AttestationRun r where r.UID_AttestationRun = @UID_AttestationRun if @DebugSwitch > 0 begin select @Message = 'Zeit gesamt            ' 
37+ STR(DATEDIFF(ss, @start, GETUTCDATE())) print @message select @Message = 'Attestationcases angelegt ' + STR(dbo.qbm_fgiTableCountall('Attestationcase'
38) - @CountAt) print @message select @Message = 'Jobs  angelegt            ' + STR(dbo.qbm_fgiTableCountall('Jobqueue') - @CountJQ) print @message end  
39if exists (select top 1 1 from #JobQueue ) begin insert into JobQueue ( ComponentAssembly, ComponentClass, DeferOnError, ErrorNotify, ExecutionType, IgnoreErrors
40, IsSplitOnly, JobChainName, MaxInstance, MinutesToDefer, Retries, ParamIN, Priority , Queue, Ready2EXE, StartAt, TaskName, UID_Job, UID_JobError, UID_JobSameServer
41, UID_JobSuccess, UID_Tree, WasError, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, IsRootJob, GenProcID , isToFreezeOnError, BasisObjectKey
42 ) select ComponentAssembly, ComponentClass, DeferOnError, ErrorNotify, ExecutionType, IgnoreErrors, IsSplitOnly, JobChainName, MaxInstance, MinutesToDefer
43, Retries, ParamIN, Priority , Queue, Ready2EXE, StartAt, TaskName, UID_Job, UID_JobError, UID_JobSameServer, UID_JobSuccess, UID_Tree, WasError, XDateInserted
44, XDateUpdated, XUserInserted, XUserUpdated, IsRootJob, GenProcID , isToFreezeOnError, BasisObjectKey from #JobQueue end if exists (select top 1 1 from
45 #QBMElementAffectedByJob ) begin insert into QBMElementAffectedByJob (UID_Job, ObjectKeyAffected) select UID_Job, ObjectKeyAffected from #QBMElementAffectedByJob
46 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 
471) WITH NOWAIT END CATCH endLabel: truncate Table #JobQueue truncate Table #QBMElementAffectedByJob return end 
48