Back to OIM Explorer

dbo.QBM_PCustomSQLCompile

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 3.363 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_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PCustomSQLFill source text reference
  • references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PProcedureDrop source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PSessionErrorClean source text reference
  • references source dbo.QBM_PTriggerDrop source text reference
  • references source dbo.QBM_PViewDrop source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL160 lines
1CREATE PROCEDURE QBM_PCustomSQLCompile2AS3BEGIN4  SET nocount5    ON6  DECLARE @scripttype nvarchar(10)7  DECLARE @scriptname nvarchar(255)8  DECLARE @scriptcode nvarchar(max)9  DECLARE @SortOrder int10  DECLARE @initialContent nvarchar(max)11  DECLARE @Work TABLE(scripttype nvarchar(10) collate database_default,12  scriptname nvarchar(255) collate database_default,13  scriptcode nvarchar(max) collate database_default,14  SortOrder int identity)15  DECLARE @AllesSchoen BIT = 116  SET XACT_ABORT OFF17  BEGIN TRY18    IF NOT EXISTS(19      SELECT TOP 1 120      FROM QBMCustomSQL)21    BEGIN22      EXEC QBM_PCustomSQLFill print 'exec QBM_PCustomSQLFill'23    END24    BEGIN25      TRANSACTION SAVE TRANSACTION CompleVersuch26      DELETE QBMCustomSQL27      EXEC QBM_PCustomSQLFill28      INSERT INTO @Work(scripttype,29      scriptname,30      scriptcode)31      SELECT32        c.scripttype,33        c.ScriptName,34        c.scriptcode35      FROM QBMCustomSQL c36      WHERE37        c.ScriptType IN(N 'N',38      N 'F')39      ORDER BY c.scripttype,40      c.SortOrder41      WHILE EXISTS(42      SELECT TOP 1 143      FROM @Work)44      BEGIN45        SELECT46          TOP 1 @scriptname = w.scriptname,47          @scripttype = w.scripttype,48          @scriptcode = w.scriptcode,49          @SortOrder = w.SortOrder50        FROM @Work w51        ORDER BY w.SortOrder52        SELECT @initialContent = N 'drop function if exists ' + rtrim(@ScriptName)53        EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @initialContent,54          @LockTimeout_ms = DEFAULT,55          @MaxWaitTimeForLock_s = DEFAULT,56          @ProcIDForJournal = @@procid,57          @HandleErrorSilent = 058        BEGIN TRY59          EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @scriptcode,60            @LockTimeout_ms = DEFAULT,61            @MaxWaitTimeForLock_s = DEFAULT,62            @ProcIDForJournal = @@procid,63            @HandleErrorSilent = 064          EXEC QBM_PSessionErrorClean65        END TRY66        BEGIN CATCH67          EXEC QBM_PSessionErrorAdd DEFAULT68          SELECT @AllesSchoen = 069        END CATCH70        DELETE @Work71        WHERE72          SortOrder = @SortOrder73      END74      DELETE @Work75      INSERT INTO @Work(scripttype,76      scriptname,77      scriptcode)78      SELECT79        c.scripttype,80        c.ScriptName,81        c.scriptcode82      FROM QBMCustomSQL c83      WHERE84        c.ScriptType IN(N 'V',85      N 'P',86      N 'T')87      ORDER BY c.SortOrder88      WHILE EXISTS(89      SELECT TOP 1 190      FROM @Work)91      BEGIN92        SELECT93          TOP 1 @scriptname = w.scriptname,94          @scripttype = w.scripttype,95          @scriptcode = w.scriptcode,96          @SortOrder = w.SortOrder97        FROM @Work w98        ORDER BY w.SortOrder99        SELECT100          @initialContent = CONCAT('exec ',101        CASE @Scripttype102          WHEN 'V' THEN103        'QBM_PViewDrop '104          WHEN 'T' THEN105        'QBM_PTriggerDrop '106        ELSE 'QBM_PProcedureDrop '107        END,108        '''',109        rtrim(@ScriptName),110        '''',111        CASE @scripttype112          WHEN 'T' THEN113        ' , @CustomTriggerAlso = 1'114        ELSE ''115        END)116        EXEC QBM_PJournal 'Element Zwangsweise gedroppt in CustomSQLCompile',117          @@PROCID,118        'E',119        'E'120        EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @initialContent,121          @LockTimeout_ms = DEFAULT,122          @MaxWaitTimeForLock_s = DEFAULT,123          @ProcIDForJournal = @@procid,124          @HandleErrorSilent = 0125        EXEC QBM_PSessionErrorClean126        BEGIN TRY127          EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @scriptcode,128            @LockTimeout_ms = DEFAULT,129            @MaxWaitTimeForLock_s = DEFAULT,130            @ProcIDForJournal = @@procid,131            @HandleErrorSilent = 0132          EXEC QBM_PSessionErrorClean133        END TRY134        BEGIN CATCH135          EXEC QBM_PSessionErrorAdd DEFAULT136          SELECT @AllesSchoen = 0137        END CATCH138        DELETE @Work139        WHERE140          SortOrder = @SortOrder141      END142      ROLLBACK TRANSACTION CompleVersuch COMMIT TRANSACTION143      IF @AllesSchoen = 0144      BEGIN145        RAISERROR('',146        18,147        1)148          WITH NOWAIT149      END150    END TRY151    BEGIN CATCH152      DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()153      RAISERROR(@Rethrow,154      18,155      1)156        WITH NOWAIT157    END CATCH158    ende:159    RETURN160  END
Open raw exported source
SQL ยท Raw23 lines
1  create   procedure QBM_PCustomSQLCompile as begin set nocount on declare @scripttype nvarchar(10) declare @scriptname nvarchar(255) declare @scriptcode2 nvarchar(max) declare @SortOrder int declare @initialContent nvarchar(max) declare @Work table (scripttype nvarchar(10) collate database_default , scriptname3 nvarchar(255) collate database_default , scriptcode nvarchar(max) collate database_default , SortOrder int identity ) declare @AllesSchoen bit = 1 SET4 XACT_ABORT OFF BEGIN TRY if not exists (select top 1 1 from QBMCustomSQL ) begin exec QBM_PCustomSQLFill print 'exec QBM_PCustomSQLFill' end  begin Transaction5 SAVE TRANSACTION CompleVersuch delete QBMCustomSQL exec QBM_PCustomSQLFill   insert into @Work(scripttype, scriptname, scriptcode) select c.scripttype6, c.ScriptName, c.scriptcode from QBMCustomSQL c where c.ScriptType in (N'N', N'F') order by c.scripttype, c.SortOrder while exists (select top 1 1 from7 @Work) BEGIN select top 1 @scriptname = w.scriptname , @scripttype = w.scripttype , @scriptcode = w.scriptcode , @SortOrder = w.SortOrder from @Work w8 order by w.SortOrder select @initialContent = N'drop function if exists ' + rtrim(@ScriptName) exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @initialContent9 , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0  BEGIN TRY exec QBM_PExecuteSQLWithRetry_LLP10 @SQLStatement = @scriptcode , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 exec11 QBM_PSessionErrorClean END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default select @AllesSchoen = 0    END CATCH delete  @Work  where SortOrder = @SortOrder12 END    delete  @Work  insert into @Work(scripttype, scriptname, scriptcode) select c.scripttype, c.ScriptName, c.scriptcode from QBMCustomSQL c where 13c.ScriptType in (N'V', N'P', N'T') order by c.SortOrder while exists (select top 1 1 from @Work) BEGIN select top 1 @scriptname = w.scriptname , @scripttype14 = w.scripttype , @scriptcode = w.scriptcode , @SortOrder = w.SortOrder from @Work w order by w.SortOrder select @initialContent = concat('exec ' , case15 @Scripttype when 'V' then 'QBM_PViewDrop ' when 'T' then 'QBM_PTriggerDrop ' else 'QBM_PProcedureDrop ' end , '''', rtrim(@ScriptName), '''' , case @scripttype16 when 'T' then ' , @CustomTriggerAlso = 1' else '' end ) exec QBM_PJournal 'Element Zwangsweise gedroppt in CustomSQLCompile', @@PROCID, 'E', 'E' exec 17QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @initialContent , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid18 , @HandleErrorSilent = 0  exec QBM_PSessionErrorClean BEGIN TRY exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @scriptcode , @LockTimeout_ms = default19 , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 exec QBM_PSessionErrorClean END TRY BEGIN CATCH exec QBM_PSessionErrorAdd20 default select @AllesSchoen = 0    END CATCH delete  @Work  where SortOrder = @SortOrder END  ROLLBACK TRANSACTION CompleVersuch Commit Transaction  if21 @AllesSchoen = 0 begin  RAISERROR ('', 18, 1) WITH NOWAIT end END TRY BEGIN CATCH declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR22 (@Rethrow, 18, 1) WITH NOWAIT END CATCH ende:  return end 23