Back to OIM Explorer

dbo.QBM_ZCheckPendingNL

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.740 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_PSessionErrorAdd source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL129 lines
1CREATE PROCEDURE QBM_ZCheckPendingNL(2  @SlotNumber int,3  @TableName varchar(38),4  @Dummy2 varchar(38),5  @DummyGenProcID varchar(38)6)7AS8BEGIN9  DECLARE @SQLCmd nvarchar(max)10  DECLARE @nummer int11  DECLARE @DebugSwitch int = 012  DECLARE @Sammler TABLE(nummer int identity,13  SQLCode nvarchar(max) collate database_default,14  Basis nvarchar(max) collate database_default)15  DECLARE @SQLVor nvarchar(max)16  DECLARE @Muster nvarchar(max) = CONCAT('17	updat',18  'e @TableName19		set @Columnname = null20		where rtrim(@Columnname) = ''''21')22  DECLARE @MusterUeberhaupt nvarchar(max) = ' or rtrim(@columnname) = ''''  '23  GOTO endLabel24  BEGIN TRY25    INSERT INTO @Sammler(SQLCode,26    Basis)27    SELECT28      replace(replace(@Muster, '@TableName', x.TableName),29      '@Columnname',30      x.ColumnName),31      replace(@MusterUeberhaupt,32      '@Columnname',33      x.ColumnName)34    FROM(35    SELECT36      tt.TableName,37      dc.ColumnName38    FROM information_schema.tables t39    JOIN information_schema.columns c40      WITH(readpast)41      ON t.TABLE_NAME = c.TABLE_NAME42    JOIN DialogTable tt43      WITH(readpast)44      ON t.TABLE_NAME = tt.TableName45    JOIN DialogColumn dc46      WITH(readpast)47      ON dc.UID_DialogTable = tt.UID_DialogTable AND dc.Columnname = c.COLUMN_NAME48    WHERE49      t.TABLE_TYPE = 'base table' AND t.TABLE_NAME = @TableName AND c.DATA_TYPE LIKE '%char' AND c.COLUMN_NAME NOT IN('XObjectKey') AND c.IS_NULLABLE50    = 'YES' AND dc.IsPKMember = 0 AND t.TABLE_NAME NOT LIKE '%AEDS%' AND NOT(t.TABLE_NAME = 'QBMCustomSQL' AND c.COLUMN_NAME = 'ScriptCode') AND NOT51    (t.TABLE_NAME = 'AttestationCase' AND c.COLUMN_NAME = 'ReportContent') AND NOT EXISTS(52    SELECT TOP 1 153    FROM QBM_VHeavyLoadTables hl54    WHERE55      hl.TableName = t.TABLE_NAME)) AS x56    SELECT @sqlcmd = ''57    SELECT @SQLVor = '#'58    WHILE @SQLVor > ' '59    BEGIN60      SELECT @SQLVor = NULL61      SELECT TOP 1 @SQLVor = s.Basis62      FROM @Sammler s63      WHERE64        s.Basis > ' '65      IF @SQLVor IS NULL66      BEGIN67        CONTINUE68      END69      SELECT @SQLCmd = @SQLCmd + nchar(13) + nchar(10) + @SQLVor70      UPDATE @Sammler71      SET Basis = NULL72      WHERE73        Basis = @SQLVor74    END75    IF @DebugSwitch > 076    BEGIN77      print @SQLCmd78    END79    SELECT80      @SQLCmd = 'select top 1 181		from ' + @TableName + ' with (nolock)82		where 1 = 0 83 ' + @SQLCmd + '				84		'85    IF @DebugSwitch > 086    BEGIN87      print @SQLCmd88    END89    EXEC sp_executesql @SQLCmd90    IF @@ROWCOUNT = 091    BEGIN92      IF @DebugSwitch > 093      BEGIN94        print 'Vorprüfung nix gefunden'95      END96      GOTO endlabel97    END98    SELECT @nummer = -199    WHILE @nummer IS NOT NULL100    BEGIN101      SELECT @nummer = NULL102      SELECT103        TOP 1 @nummer = s.nummer,104        @SQLCmd = s.SQLCode105      FROM @Sammler s106      IF @nummer IS NULL107      BEGIN108        CONTINUE109      END110      IF @DebugSwitch > 0111      BEGIN112        print @sqlcmd113      END114      EXEC sp_executeSQL @sqlcmd115      DELETE @Sammler116      WHERE117        nummer = @nummer118    END119  END TRY120  BEGIN CATCH121    EXEC QBM_PSessionErrorAdd DEFAULT122    RAISERROR('',123    18,124    1)125      WITH NOWAIT126  END CATCH127  endLabel:128  RETURN129END
Open raw exported source
SQL · Raw27 lines
1    create   procedure QBM_ZCheckPendingNL ( @SlotNumber int , @TableName varchar(38) , @Dummy2 varchar(38) , @DummyGenProcID varchar(38) ) as begin2 declare @SQLCmd nvarchar(max) declare @nummer int declare @DebugSwitch int = 0 declare @Sammler table (nummer int identity , SQLCode nvarchar(max) collate3 database_default , Basis nvarchar(max) collate database_default ) declare @SQLVor nvarchar(max) declare @Muster nvarchar(max) = concat('4	updat','e @TableName5		set @Columnname = null6		where rtrim(@Columnname) = ''''7'8 ) declare @MusterUeberhaupt nvarchar(max) = ' or rtrim(@columnname) = ''''  '  goto endLabel BEGIN TRY insert into @Sammler(SQLCode, Basis) select replace9(replace(@Muster , '@TableName', x.TableName ) , '@Columnname', x.ColumnName) , replace(@MusterUeberhaupt , '@Columnname', x.ColumnName) from ( select 10tt.TableName, dc.ColumnName from information_schema.tables t join information_schema.columns c with (readpast) on t.TABLE_NAME = c.TABLE_NAME  join DialogTable11 tt with (readpast) on t.TABLE_NAME = tt.TableName join DialogColumn dc with (readpast) on dc.UID_DialogTable = tt.UID_DialogTable and dc.Columnname = 12c.COLUMN_NAME where t.TABLE_TYPE = 'base table' and t.TABLE_NAME = @TableName and c.DATA_TYPE like '%char' and c.COLUMN_NAME not in ('XObjectKey') and 13c.IS_NULLABLE = 'YES' and dc.IsPKMember = 0 and t.TABLE_NAME not like '%AEDS%'  and not (t.TABLE_NAME = 'QBMCustomSQL' and c.COLUMN_NAME = 'ScriptCode'14)  and not (t.TABLE_NAME = 'AttestationCase' and c.COLUMN_NAME = 'ReportContent') and not exists (select top 1 1 from QBM_VHeavyLoadTables hl where hl.TableName15 = t.TABLE_NAME  ) ) as x select @sqlcmd = '' select @SQLVor = '#' while @SQLVor > ' ' begin select @SQLVor = null select top 1 @SQLVor = s.Basis from 16@Sammler s where s.Basis > ' ' if @SQLVor is null begin continue end     select @SQLCmd = @SQLCmd + nchar(13) + nchar(10) + @SQLVor update @Sammler set17 Basis = null where Basis = @SQLVor end  if @DebugSwitch > 0 begin print @SQLCmd end select @SQLCmd = 'select top 1 118		from ' + @TableName + ' with (nolock)19		where 1 = 0 20 '21 + @SQLCmd + '				22		' if @DebugSwitch > 0 begin print @SQLCmd end exec sp_executesql @SQLCmd if @@ROWCOUNT = 0 begin if @DebugSwitch > 0 begin print 23'Vorprüfung nix gefunden' end goto endlabel end  select @nummer = -1 while @nummer is not null begin select @nummer = null select top 1 @nummer = s.nummer24 , @SQLCmd = s.SQLCode from @Sammler s if @nummer is null begin continue end if @DebugSwitch > 0 begin print @sqlcmd end exec sp_executeSQL @sqlcmd delete25  @Sammler  where nummer = @nummer end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel:  return26 end 27