Back to OIM Explorer

dbo.ATT_PAttestationCaseCreate

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 7.079 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_FTHexPattern source text reference
  • references source dbo.QBM_FCVElementToObjectKey1 source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_FGITableCountAll source text reference
  • references source dbo.ATT_PAttestationCaseCreate_i source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QER_PGIQERWorkingMethod source text reference

References

Referenced By

Complete Source

SQL292 lines
1CREATE PROCEDURE ATT_PAttestationCaseCreate(2  @UID_AttestationRun varchar(38),3  @UID_AttestationPolicy varchar(38),4  @TableName varchar(30),5  @WhereClause nvarchar(max)6)7AS8BEGIN9  DECLARE @start datetime = getutcdate()10  DECLARE @AnzahlElemente int11  DECLARE @SQLCmd nvarchar(max)12  DECLARE @Xdate datetime = getutcdate()13  DECLARE @XUser nvarchar(64) = dbo.QBM_FGISessionContext('XUser')14  DECLARE @UID_QERWorkingMethod varchar(38)15  DECLARE @UID_PWODecisionMethod varchar(38)16  DECLARE @Message nvarchar(1024)17  DECLARE @DebugSwitch int = 018  IF @DebugSwitch > 019  BEGIN20    DECLARE @CountAt int = dbo.qbm_fgiTableCountall('Attestationcase')21    DECLARE @CountJQ int = dbo.qbm_fgiTableCountall('Jobqueue')22    DECLARE @CountPWO int = dbo.qbm_fgiTableCountall(@TableName) print '@CountAt  ' + str(@CountAt) print '@CountJQ  ' + str(@CountJQ) print '@CountPWO '23    + str(@CountPWO)24  END25  SELECT TOP 1 @UID_PWODecisionMethod = p.UID_PWODecisionMethod26  FROM AttestationPolicy p27  WHERE28    p.UID_AttestationPolicy = @UID_AttestationPolicy29  EXEC QER_PGIQERWorkingMethod @UID_PWODecisionMethod,30  '',31    @UID_QERWorkingMethod OUTPUT32  DECLARE @Zwischenlager QBM_YParameterList33  DECLARE @Rohware QBM_YParameterList34  DECLARE @HexPatterns QBM_YCursorBuffer35  DECLARE @CountChunks int36  DECLARE @CountChunksWithData int = 037  DECLARE @ChunkWithData int38  DECLARE @ElementIndex int39  DECLARE @HexPattern varchar(200)40  SET XACT_ABORT OFF41  BEGIN TRY42    SET nocount43      ON44    IF object_id('tempdb..#JobQueue') IS NULL45    BEGIN46      CREATE TABLE #JobQueue(47        UID_Job varchar(38) collate database_default NOT NULL,48        UID_JobSameServer varchar(38) collate database_default NULL,49        UID_Tree varchar(38) collate database_default NOT NULL,50        Queue nvarchar(128) collate database_default NOT NULL,51        ComponentClass nvarchar(200) collate database_default NULL,52        ExecutionType nvarchar(16) collate database_default NULL,53        ComponentAssembly nvarchar(255) collate database_default NULL,54        TaskName nvarchar(64) collate database_default NOT NULL,55        ParamIN nvarchar(max) collate database_default NULL,56        Ready2EXE nvarchar(32) collate database_default NOT NULL,57        StartAt datetime NULL,58        XDateInserted datetime NULL,59        XDateUpdated datetime NULL,60        XUserInserted nvarchar(64) collate database_default NULL,61        XUserUpdated nvarchar(64) collate database_default NULL,62        Retries int DEFAULT 0 NULL,63        Priority int DEFAULT 0 NULL,64        DeferOnError BIT DEFAULT 0 NULL,65        MinutesToDefer int DEFAULT 0 NULL,66        UID_JobError varchar(38) collate database_default NULL,67        UID_JobSuccess varchar(38) collate database_default NULL,68        IgnoreErrors BIT DEFAULT 0 NULL,69        MaxInstance int DEFAULT 0 NULL,70        JobChainName nvarchar(255) collate database_default NOT NULL,71        IsSplitOnly BIT DEFAULT 0 NULL,72        WasError BIT DEFAULT 0 NULL,73        ErrorNotify BIT DEFAULT 0 NULL,74        IsRootJob BIT DEFAULT 0 NULL,75        GenProcID varchar(38) collate database_default NOT NULL,76        IsToFreezeOnError BIT DEFAULT 0 NULL,77        BasisObjectKey varchar(138) collate database_default NULL,78        primary key nonCLUSTERED(UID_Job),79        index #JobQueue(ComponentClass,80        TaskName,81        JobChainName) INCLUDE(UID_Job)82      )83    END84    ELSE85    BEGIN86      truncate TABLE #JobQueue87    END88    IF object_id('tempdb..#QBMElementAffectedByJob'89  ) IS NULL90  BEGIN91    CREATE TABLE #QBMElementAffectedByJob(92      UID_Job varchar(38) collate database_default NOT NULL,93      ObjectKeyAffected varchar(138) collate database_default NOT NULL,94      index #QBMElementAffectedByJob(UID_Job)95    )96  END97  ELSE98  BEGIN99    truncate TABLE #QBMElementAffectedByJob100  END101  SELECT102    @SQLCmd = CONCAT('select XObjectkey 103							from ',104    @TableName,105    '106							where ',107    @WhereClause108    )109INSERT INTO @Rohware(Parameter1110)111EXEC sp_executesql @SQLCmd112IF @DebugSwitch > 0113BEGIN114  SELECT115    @Message = 'Zeit für Rohware       ' + STR(DATEDIFF(ss,116    @start,117    GETUTCDATE())118    ) print @message119END120INSERT INTO @Zwischenlager(Parameter1,121Parameter2,122Parameter3123)124SELECT125  dbo.QBM_FCVElementToObjectKey1('AttestationCase',126  'UID_AttestationCase',127  x.UID_AttestationCase128  ),129  x.UID_AttestationCase,130  x.ObjectKeyBase131FROM(132SELECT NEWID()133AS134UID_AttestationCase,135r.Parameter1 AS ObjectKeyBase136FROM @Rohware r) x137IF @DebugSwitch > 0138BEGIN139  SELECT140    @Message = 'Zeit für Zwischenlager ' + STR(DATEDIFF(ss, @start, GETUTCDATE())) print @message141END142SELECT @AnzahlElemente = COUNT(*)143FROM @Zwischenlager144INSERT INTO @HexPatterns(Ident1)145SELECT pat.HexPattern146FROM dbo.QBM_FTHexPattern(CASE147WHEN @AnzahlElemente / 800 < 16 THEN1481149WHEN @AnzahlElemente / 800 < 256 THEN1502151WHEN @AnzahlElemente / 800 < 4096 THEN1523153ELSE 4154END) AS pat155SELECT @CountChunks = @@ROWCOUNT156SELECT @ElementIndex = 1157IF @DebugSwitch > 0158BEGIN159  SELECT160    @Message = CONCAT('Count Rohware ',161    STR(count(*)))162  FROM @Rohware print @message163END164WHILE @ElementIndex <= @CountChunks165BEGIN166  SELECT TOP 1 @HexPattern = p.Ident1167  FROM @HexPatterns p168  WHERE169    p.ElementIndex = @ElementIndex170  IF @DebugSwitch > 0171  BEGIN172    print @HexPattern173  END174  EXEC ATT_PAttestationCaseCreate_i @Zwischenlager,175    @UID_AttestationRun,176    @UID_AttestationPolicy,177    @UID_QERWorkingMethod,178    @HexPattern,179    @Xdate,180    @XUser,181    @ChunkWithData OUTPUT182  SELECT @CountChunksWithData += @ChunkWithData183  SELECT @ElementIndex += 1184END185UPDATE AttestationRun186SET CountChunksUnderConstruction += @CountChunksWithData,187XDateUpdated = @Xdate,188XUserUpdated = @XUser189FROM AttestationRun r190WHERE191  r.UID_AttestationRun = @UID_AttestationRun192IF @DebugSwitch > 0193BEGIN194  SELECT195    @Message = 'Zeit gesamt            ' + STR(DATEDIFF(ss, @start, GETUTCDATE())) print @message196  SELECT197    @Message = 'Attestationcases angelegt ' + STR(dbo.qbm_fgiTableCountall('Attestationcase') - @CountAt) print @message198  SELECT199    @Message = 'Jobs  angelegt            ' + STR(dbo.qbm_fgiTableCountall('Jobqueue') - @CountJQ) print @message200END201IF EXISTS(202  SELECT TOP 1 1203  FROM #JobQueue)204BEGIN205  INSERT INTO JobQueue(ComponentAssembly,206  ComponentClass,207  DeferOnError,208  ErrorNotify,209  ExecutionType,210  IgnoreErrors,211  IsSplitOnly,212  JobChainName,213  MaxInstance,214  MinutesToDefer,215  Retries,216  ParamIN,217  Priority,218  Queue,219  Ready2EXE,220  StartAt,221  TaskName,222  UID_Job,223  UID_JobError,224  UID_JobSameServer,225  UID_JobSuccess,226  UID_Tree,227  WasError,228  XDateInserted,229  XDateUpdated,230  XUserInserted,231  XUserUpdated,232  IsRootJob,233  GenProcID,234  isToFreezeOnError,235  BasisObjectKey)236  SELECT237    ComponentAssembly,238    ComponentClass,239    DeferOnError,240    ErrorNotify,241    ExecutionType,242    IgnoreErrors,243    IsSplitOnly,244    JobChainName,245    MaxInstance,246    MinutesToDefer,247    Retries,248    ParamIN,249    Priority,250    Queue,251    Ready2EXE,252    StartAt,253    TaskName,254    UID_Job,255    UID_JobError,256    UID_JobSameServer,257    UID_JobSuccess,258    UID_Tree,259    WasError,260    XDateInserted,261    XDateUpdated,262    XUserInserted,263    XUserUpdated,264    IsRootJob,265    GenProcID,266    isToFreezeOnError,267    BasisObjectKey268  FROM #JobQueue269END270IF EXISTS(271  SELECT TOP 1 1272  FROM #QBMElementAffectedByJob)273BEGIN274  INSERT INTO QBMElementAffectedByJob(UID_Job,275  ObjectKeyAffected)276  SELECT277    UID_Job,278    ObjectKeyAffected279  FROM #QBMElementAffectedByJob280END281END TRY282BEGIN CATCH283  EXEC QBM_PSessionErrorAdd DEFAULT284  DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()285  RAISERROR(@Rethrow,286  18,287  1)288    WITH NOWAIT289END CATCH290endLabel: truncate TABLE #JobQueue truncate TABLE #QBMElementAffectedByJob291RETURN292END
Open raw exported source
SQL · Raw48 lines
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 @Xdate3 datetime = getutcdate() declare @XUser nvarchar(64) = dbo.QBM_FGISessionContext('XUser') declare @UID_QERWorkingMethod varchar(38) declare @UID_PWODecisionMethod4 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(@CountAt6) print '@CountJQ  ' + str(@CountJQ) print '@CountPWO ' + str(@CountPWO) end select top 1 @UID_PWODecisionMethod = p.UID_PWODecisionMethod from AttestationPolicy7 p where p.UID_AttestationPolicy = @UID_AttestationPolicy exec QER_PGIQERWorkingMethod @UID_PWODecisionMethod, ''  , @UID_QERWorkingMethod output declare8 @Zwischenlager QBM_YParameterList    declare @Rohware QBM_YParameterList  declare @HexPatterns QBM_YCursorBuffer declare @CountChunks int declare @CountChunksWithData9 int = 0 declare @ChunkWithData int declare @ElementIndex int declare @HexPattern varchar(200) SET XACT_ABORT OFF BEGIN TRY set nocount on  if object_id10('tempdb..#JobQueue') is null begin  Create Table #JobQueue( UID_Job varchar(38) collate database_default NOT NULL , UID_JobSameServer varchar(38) collate11 database_default NULL , UID_Tree varchar(38) collate database_default NOT NULL , Queue nvarchar(128) collate database_default NOT NULL , ComponentClass12 nvarchar(200) collate database_default NULL , ExecutionType nvarchar(16) collate database_default NULL , ComponentAssembly nvarchar(255) collate database_default13 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_default15 NULL , XUserUpdated nvarchar(64) collate database_default NULL , Retries int default 0 NULL , Priority int default 0 NULL , DeferOnError bit default 016 NULL , MinutesToDefer int default 0 NULL , UID_JobError varchar(38) collate database_default NULL , UID_JobSuccess varchar(38) collate database_default17 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) collate19 database_default NOT NULL  , IsToFreezeOnError bit default 0 NULL   , BasisObjectKey varchar(138) collate database_default NULL      , primary key nonCLUSTERED20 (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_default22 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 @Message26 = 'Zeit für Rohware       ' + STR(DATEDIFF(ss, @start, GETUTCDATE())) print @message end insert into @Zwischenlager (Parameter1, Parameter2, Parameter327) select dbo.QBM_FCVElementToObjectKey1('AttestationCase', 'UID_AttestationCase', x.UID_AttestationCase) , x.UID_AttestationCase , x.ObjectKeyBase from28 ( 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 @AnzahlElemente31 / 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 where33 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 , XUserUpdated36 = @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, IgnoreErrors40, IsSplitOnly, JobChainName, MaxInstance, MinutesToDefer, Retries, ParamIN, Priority , Queue, Ready2EXE, StartAt, TaskName, UID_Job, UID_JobError, UID_JobSameServer41, UID_JobSuccess, UID_Tree, WasError, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, IsRootJob, GenProcID , isToFreezeOnError, BasisObjectKey42 ) select ComponentAssembly, ComponentClass, DeferOnError, ErrorNotify, ExecutionType, IgnoreErrors, IsSplitOnly, JobChainName, MaxInstance, MinutesToDefer43, Retries, ParamIN, Priority , Queue, Ready2EXE, StartAt, TaskName, UID_Job, UID_JobError, UID_JobSameServer, UID_JobSuccess, UID_Tree, WasError, XDateInserted44, XDateUpdated, XUserInserted, XUserUpdated, IsRootJob, GenProcID , isToFreezeOnError, BasisObjectKey from #JobQueue end if exists (select top 1 1 from45 #QBMElementAffectedByJob ) begin insert into QBMElementAffectedByJob (UID_Job, ObjectKeyAffected) select UID_Job, ObjectKeyAffected from #QBMElementAffectedByJob46 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