Back to OIM Explorer

dbo.ATT_PHelperAttestationPolicy

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 5.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_FCVStringToGUID source text reference
  • references source dbo.QBM_FCVStringToGUIDMAll source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.ATT_P59CCD84DFA68B81E1B20B65_ source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PTransactionLevelCheck source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL208 lines
1CREATE PROCEDURE ATT_PHelperAttestationPolicy(2  @UID_AttestationPolicy varchar(38),3  @WhereClause nvarchar(max),4  @TableName varchar(30)5)6AS7BEGIN8  DECLARE @SQLcmd nvarchar(max)9  DECLARE @Sollzustand QBM_YParameterList10  DECLARE @Einzufuegen QBM_YParameterList11  DECLARE @ZuLoeschen QBM_YParameterList12  DECLARE @WhereClauseJob nvarchar(max)13  DECLARE @GenProcID varchar(38) = dbo.QBM_FGISessionContext('')14  DECLARE @DebugSwitch int = 015  DECLARE @DebugStart datetime = getutcdate()16  DECLARE @HexPatterns QBM_YCursorBuffer17  DECLARE @CountChunks int18  DECLARE @ElementIndex int19  DECLARE @AnzahlElemente int20  DECLARE @HexPattern varchar(200)21  DECLARE @isInactive BIT22  DECLARE @IsShowElementsInvolved BIT23  SET XACT_ABORT OFF24  BEGIN TRY25    SET nocount26      ON27    EXEC QBM_PTransactionLevelCheck 0,28      @@procid29    SELECT30      TOP 1 @isInactive = p.IsInActive,31      @IsShowElementsInvolved = p.IsShowElementsInvolved32    FROM AttestationPolicy p33    WHERE34      uid_AttestationPolicy = @uid_AttestationPolicy35    IF @isInactive = 1 OR @IsShowElementsInvolved = 036    BEGIN37      IF @DebugSwitch > 038      BEGIN39        print 'sofortausstieg wegen inaktiv'40      END41      GOTO DeltaBestimmen42    END43    SELECT44      @SQLcmd = CONCAT('select XObjectKey45		from ',46      @TableName,47      N '48		where ',49      @WhereClause)50    INSERT INTO @Sollzustand(Parameter1)51    EXEC sp_executeSQL @SQLCmd52    IF @DebugSwitch > 053    BEGIN54      print 'Soll befüllen' + str(datediff(ss, @DebugStart, getutcdate()))55      SELECT COUNT(*) AS AnzahlSoll56      FROM @Sollzustand57      SELECT @DebugStart = GETUTCDATE()58    END59    DeltaBestimmen:60    INSERT INTO @ZuLoeschen(Parameter1)61    SELECT h.UID_HelperAttestationPolicy62    FROM HelperAttestationPolicy h63    LEFT64    OUTER65    JOIN @Sollzustand s66      ON h.UID_AttestationPolicy = @UID_AttestationPolicy AND h.ObjectKeyBase = s.Parameter167    WHERE68      h.UID_AttestationPolicy = @UID_AttestationPolicy AND s.Parameter1 IS NULL69    IF @DebugSwitch > 070    BEGIN71      print 'zu löschen befüllen' + str(datediff(ss, @DebugStart, getutcdate()))72      SELECT COUNT(*) AS AnzahlLoeschen73      FROM @ZuLoeschen74      SELECT @DebugStart = GETUTCDATE()75    END76    INSERT INTO @Einzufuegen(Parameter1,77    Parameter2,78    Parameter3)79    SELECT80      dbo.QBM_FCVStringToGUIDMAll(NULL,81      @UID_AttestationPolicy,82      s.Parameter1) AS UID_HelperAttestationPolicy,83      s.Parameter1,84      dbo.QBM_FCVElementToObjectKey1('HelperAttestationPolicy',85      'UID_HelperAttestationPolicy',86      dbo.QBM_FCVStringToGUIDMAll(NULL, @UID_AttestationPolicy, s.Parameter1)) AS XObjectkey87    FROM @Sollzustand s88    LEFT89    OUTER90    JOIN HelperAttestationPolicy h91      ON h.UID_AttestationPolicy = @UID_AttestationPolicy AND s.Parameter1 = h.ObjectKeyBase92    WHERE93      h.UID_HelperAttestationPolicy IS NULL94    IF @DebugSwitch > 095    BEGIN96      print 'einzufügen befüllen' + str(datediff(ss, @DebugStart, getutcdate()))97      SELECT COUNT(*) AS AnzahlEinzufuegen98      FROM @Einzufuegen99      SELECT @DebugStart = GETUTCDATE()100    END101    SELECT @AnzahlElemente = COUNT(*)102    FROM @ZuLoeschen103    IF @AnzahlElemente > 0104    BEGIN105      DELETE @HexPatterns106      INSERT INTO @HexPatterns(Ident1)107      SELECT pat.HexPattern108      FROM dbo.QBM_FTHexPattern(CASE109      WHEN @AnzahlElemente / 50000 < 16 THEN110      1111      WHEN @AnzahlElemente / 50000 < 256 THEN112      2113      WHEN @AnzahlElemente / 50000 < 4096 THEN114      3115      ELSE 4116      END) AS pat117      SELECT @CountChunks = @@ROWCOUNT118      SELECT @ElementIndex = 1119      WHILE @ElementIndex <= @CountChunks120      BEGIN121        SELECT TOP 1 @HexPattern = p.Ident1122        FROM @HexPatterns p123        WHERE124          p.ElementIndex = @ElementIndex125        IF @DebugSwitch > 0126        BEGIN127          print @HexPattern128        END129        DELETE HelperAttestationPolicy130        FROM HelperAttestationPolicy h131        JOIN @ZuLoeschen l132          ON h.UID_HelperAttestationPolicy = l.Parameter1133        WHERE134          l.Parameter1 LIKE @HexPattern135        SELECT @ElementIndex += 1136      END137    END138    IF @DebugSwitch > 0139    BEGIN140      print 'gelöscht ' + str(datediff(ss, @DebugStart, getutcdate()))141      SELECT @DebugStart = GETUTCDATE()142    END143    INSERT INTO HelperAttestationPolicy(ObjectKeyBase,144    UID_AttestationPolicy,145    UID_HelperAttestationPolicy,146    XObjectKey)147    SELECT148      e.Parameter2 AS ObjectKeyBase,149      @UID_AttestationPolicy,150      e.Parameter1 AS UID_HelperAttestationPolicy,151      e.Parameter3 AS XObjectkey152    FROM @Einzufuegen e153    IF @DebugSwitch > 0154    BEGIN155      print 'eingefügt ' + str(datediff(ss, @DebugStart, getutcdate()))156      SELECT @DebugStart = GETUTCDATE()157    END158    SELECT @AnzahlElemente = COUNT(*)159    FROM @Einzufuegen160    IF @AnzahlElemente > 0161    BEGIN162      DELETE @HexPatterns163      INSERT INTO @HexPatterns(Ident1)164      SELECT pat.HexPattern165      FROM dbo.QBM_FTHexPattern(CASE166      WHEN @AnzahlElemente / 4000 < 16 THEN167      1168      WHEN @AnzahlElemente / 4000 < 256 THEN169      2170      WHEN @AnzahlElemente / 4000 < 4096 THEN171      3172      ELSE 4173      END) AS pat174      SELECT @CountChunks = @@ROWCOUNT175      SELECT @ElementIndex = 1176      WHILE @ElementIndex <= @CountChunks177      BEGIN178        SELECT TOP 1 @HexPattern = p.Ident1179        FROM @HexPatterns p180        WHERE181          p.ElementIndex = @ElementIndex182        IF @DebugSwitch > 0183        BEGIN184          print @HexPattern185        END186        EXEC ATT_P59CCD84DFA68B81E1B20B65_ @Einzufuegen,187          @HexPattern,188          @GenProcID189        SELECT @ElementIndex += 1190      END191    END192    IF @DebugSwitch > 0193    BEGIN194      print 'jobs erzeugen' + str(datediff(ss, @DebugStart, getutcdate()))195      SELECT @DebugStart = GETUTCDATE()196    END197  END TRY198  BEGIN CATCH199    EXEC QBM_PSessionErrorAdd DEFAULT200    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()201    RAISERROR(@Rethrow,202    18,203    1)204      WITH NOWAIT205  END CATCH206  ende:207  RETURN208END
Open raw exported source
SQL · Raw36 lines
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_YParameterList3  declare @WhereClauseJob nvarchar(max) declare @GenProcID varchar(38) = dbo.QBM_FGISessionContext('') declare @DebugSwitch int = 0 declare @DebugStart4 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_PTransactionLevelCheck6 0, @@procid select top 1 @isInactive = p.IsInActive , @IsShowElementsInvolved = p.IsShowElementsInvolved from AttestationPolicy p where uid_AttestationPolicy7 = @uid_AttestationPolicy if @isInactive = 1 or @IsShowElementsInvolved = 0 begin if @DebugSwitch > 0 begin print 'sofortausstieg wegen inaktiv' end goto8 DeltaBestimmen end select @SQLcmd = concat('select XObjectKey9		from ' , @TableName , N'10		where ' , @WhereClause ) insert into @Sollzustand(Parameter111) exec sp_executeSQL @SQLCmd if @DebugSwitch > 0 begin print 'Soll befüllen' + str(datediff(ss, @DebugStart, getutcdate())) select COUNT(*) as AnzahlSoll12 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_AttestationPolicy14 = @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_FCVElementToObjectKey117('HelperAttestationPolicy', 'UID_HelperAttestationPolicy', dbo.QBM_FCVStringToGUIDMAll(null, @UID_AttestationPolicy, s.Parameter1 ) ) as XObjectkey from18 @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 COUNT20(*) as AnzahlEinzufuegen from @Einzufuegen select @DebugStart = GETUTCDATE() end  select @AnzahlElemente = COUNT(*) from @ZuLoeschen if @AnzahlElemente21 > 0 begin delete @HexPatterns insert into @HexPatterns (Ident1) select pat.HexPattern from dbo.QBM_FTHexPattern(case when @AnzahlElemente / 50000 < 1622 then 1 when @AnzahlElemente / 50000 < 256 then 2 when @AnzahlElemente / 50000 < 4096 then 3 else 4 end) as pat select @CountChunks = @@ROWCOUNT select23 @ElementIndex = 1 while @ElementIndex <= @CountChunks begin select top 1 @HexPattern = p.Ident1 from @HexPatterns p where p.ElementIndex = @ElementIndex24 if @DebugSwitch > 0 begin print @HexPattern end delete HelperAttestationPolicy from HelperAttestationPolicy h join @ZuLoeschen l on h.UID_HelperAttestationPolicy25 = 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_HelperAttestationPolicy27, XObjectKey ) select e.Parameter2 as ObjectKeyBase, @UID_AttestationPolicy , e.Parameter1 as UID_HelperAttestationPolicy, e.Parameter3 as XObjectkey from28 @Einzufuegen e if @DebugSwitch > 0 begin print 'eingefügt ' + str(datediff(ss, @DebugStart, getutcdate())) select @DebugStart = GETUTCDATE() end  select29 @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 then31 3 else 4 end) as pat select @CountChunks = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @CountChunks begin select top 1 @HexPattern = p.Ident132 from @HexPatterns p where p.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print @HexPattern end exec ATT_P59CCD84DFA68B81E1B20B65_ @Einzufuegen33 , @HexPattern , @GenProcID select @ElementIndex += 1 end end  if @DebugSwitch > 0 begin print 'jobs erzeugen' + str(datediff(ss, @DebugStart, getutcdate34())) select @DebugStart = GETUTCDATE() end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow35() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH  ende: return end 36