Back to OIM Explorer

dbo.QBM_PBufferT_ProcessInsert

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 6.624 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_FGIColumnDataLen source text reference
  • references source dbo.QBM_FGIColumnDataType source text reference
  • references source dbo.QBM_FGIColumnExists source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_FSQCVBufferToColumn source text reference
  • references source dbo.QBM_FSQIsNullClauseSet source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL276 lines
1CREATE PROCEDURE QBM_PBufferT_ProcessInsert(2  @ModuleName varchar(3),3  @TableName varchar(30)4)5AS6BEGIN7  DECLARE @cmd nvarchar(max)8  DECLARE @insertlist nvarchar(max)9  DECLARE @SelectList nvarchar(max)10  DECLARE @JoinList nvarchar(max)11  DECLARE @Vortest nvarchar(max)12  DECLARE @number int = 013  DECLARE @columnname varchar(30)14  DECLARE @MissingColumnname varchar(30)15  DECLARE @NumberTableSynonym nvarchar(64)16  DECLARE @ColumnDatatype nvarchar(64)17  DECLARE @ColumnDataMaxLen int18  DECLARE @countColumnsInRow int19  DECLARE @RowsPerShot int20  DECLARE @lauf int21  DECLARE @XUser nvarchar(64) = object_name(@@procid)22  DECLARE @DebugLevel char(1) = 'W'23  DECLARE @DebugSwitch int = 024  DECLARE @columnPattern nvarchar(max)25  DECLARE @ErrorMessage nvarchar(4000)26  DECLARE @ErrorSeverity int27  DECLARE @ErrorState int28  DECLARE @ElementBuffer QBM_YCursorBuffer29  DECLARE @ElementCount int30  DECLARE @ElementIndex int31  SET XACT_ABORT OFF32  BEGIN TRY33    SELECT @countColumnsInRow = count(DISTINCT columnname)34    FROM #QBMBufferTransferOneTable35    IF @countColumnsInRow = 036    BEGIN37      SELECT38        @cmd = '#LDS#Table {0} not inserted, no Column content delivered|' + @TableName + '|'39      EXEC QBM_PJournal @cmd,40        @@procid,41      'D',42        @DebugLevel43      GOTO EndLabel44    END45    IF NOT EXISTS(46      SELECT TOP 1 147      FROM QBM_VStartupTables su48      WHERE49        su.TableName = @TableName)50    BEGIN51      SELECT @MissingColumnname = NULL52      SELECT TOP 1 @MissingColumnname = x.ColumnName53      FROM(54      SELECT55        DISTINCT bu.ColumnName56      FROM #QBMBufferTransferOneTable bu) AS x57      LEFT58      OUTER59      JOIN(60      SELECT columnname61      FROM DialogColumn c62        WITH(readpast)63      JOIN DialogTable t64        WITH(readpast)65        ON c.UID_DialogTable = t.UID_DialogTable66      WHERE67        t.TableName = @TableName) AS y68        ON x.ColumnName = y.ColumnName69      WHERE70        y.ColumnName IS NULL71      IF @MissingColumnname > ' '72      BEGIN73        SELECT74          @cmd = '#LDS#Table {0} not inserted because of missing ColumnDefinition {1}, processing module {2}|' + @TableName + '|' + @MissingColumnname75          + '|' + @Modulename + '|'76        EXEC QBM_PJournal @cmd,77          @@procid,78        'W',79          @DebugLevel80        GOTO EndLabel81      END82    END83    IF @DebugSwitch > 084    BEGIN85      print '@countColumnsInRow' + str(@countColumnsInRow)86    END87    IF @countColumnsInRow < 10088    BEGIN89      SELECT @RowsPerShot = 400000 / @countColumnsInRow90    END91    ELSE92    BEGIN93      SELECT @RowsPerShot = 400094    END95    SELECT96      @columnPattern = N '97		join ( select @ContentClause@ as @columnname@, bux.ObjectkeyOfRow as XObjectKey98					from #QBMBufferTransferOneTable bux 99					where bux.columnname = ''@columnname@''100				) as @number@ on v000.XObjectKey = @number@.XObjectKey'101    SELECT102      @Vortest = N '103declare @missingList QBM_YParameterList -- t a b l e (ObjectkeyOfRow varchar(138)  -->Parameter1104105inser' + 't into @missingList(Parameter1 /*ObjectkeyOfRow*/)106select top '107      + STR(@RowsPerShot) + ' y.ObjectKeyOfRow108 from (109		select ot.ObjectKeyOfRow -- , COUNT(*) as CountItems110			from #QBMBufferTransferOneTable ot 111			group by ot.ObjectKeyOfRow112			having COUNT(*) =  '113      + str(@countColumnsInRow) + '114		) as y	115		where Not exists (select top 1 1116						 from ' + @TableName + ' x117						 where x.XObjectKey = y.ObjectkeyOfRow118						)119120'121    SELECT122      @insertlist = @Vortest + N '123 if @@rowcount = 0124  begin125	goto EndLabel126  end	127  					128Inser' + 't into ' + @TableName + ' (XObjectKey'129    SELECT @SelectList = N '130	select v000.XObjectKey '131    SELECT132      @JoinList = N '133from 134 (135	-- bestimmen aller einzufügenden Sätze136	select Parameter1 /*ObjectkeyOfRow*/ as XObjectKey 137		from @missingList138	) as v000'139    IF @TableName IN(140      SELECT TableName141      FROM QBM_VStartupTables)142    BEGIN143      INSERT INTO @ElementBuffer(Ident1)144      SELECT145        DISTINCT bu.columnname146      FROM QBM_VSchemaColumns cc147      JOIN #QBMBufferTransferOneTable bu148        ON cc.COLUMN_NAME = bu.columnname collate database_default149      WHERE150        cc.TABLE_NAME = @TableName AND(bu.HasContentFull = 0 AND isnull(bu.ContentShort, '') > ' ' OR bu.HasContentFull = 1 AND isnull(bu.ContentFull151      , '') > ' ')152      ORDER BY 1153    END154    ELSE155    BEGIN156      INSERT INTO @ElementBuffer(Ident1)157      SELECT158        DISTINCT bu.columnname159      FROM dialogColumn cc160        WITH(readpast)161      JOIN #QBMBufferTransferOneTable bu162        ON cc.COLUMNNAME = bu.columnname163      JOIN DialogTable t164        WITH(readpast)165        ON cc.UID_DialogTable = t.UID_DialogTable166      WHERE167        t.TableName = @TableName AND(bu.HasContentFull = 0 AND isnull(bu.ContentShort, '') > ' ' OR bu.HasContentFull = 1 AND isnull(bu.ContentFull168      , '') > ' ')169      ORDER BY 1170    END171    SELECT @ElementCount = count(*)172    FROM @ElementBuffer173    SELECT @ElementIndex = 1174    WHILE @ElementIndex <= @ElementCount175    BEGIN176      SELECT TOP 1 @ColumnName = bu.Ident1177      FROM @ElementBuffer bu178      WHERE179        bu.ElementIndex = @ElementIndex180      SELECT @number += 1181      SELECT @NumberTableSynonym = 'v' +182      RIGHT('00' + ltrim(str(@number)),183      3)184      IF @DebugSwitch > 0185      BEGIN186        print 'Spalte : ' + @columnname187      END188      SELECT189        @ColumnDatatype = dbo.QBM_FGIColumnDataType(@TableName,190        @columnname)191      SELECT192        @ColumnDataMaxLen = dbo.QBM_FGIColumnDataLen(@TableName,193        @columnname)194      SELECT195        @insertlist = @insertlist + ', ' + @columnname196      SELECT197        @SelectList = @SelectList + N '	198			, ' + dbo.QBM_FSQIsNullClauseSet(@TableName,199        @columnname,200        @NumberTableSynonym)201      SELECT202        @JoinList = @JoinList + replace(REPLACE(REPLACE(@columnPattern, '@ContentClause@', dbo.QBM_FSQCVBufferToColumn(@TableName,203        @columnname, N 'bux', 0)), '@columnname@', @columnname),204        '@number@',205        @NumberTableSynonym)206      SELECT @ElementIndex += 1207    END208    IF dbo.QBM_FGIColumnExists(@TableName,209    'XUserInserted') = 1210    BEGIN211      SELECT212        @insertlist = @insertlist + ', XuserInserted, XUserUpdated, XDateInserted, XDateupdated'213      SELECT214        @SelectList = @SelectList + ', ''' + @xuser + ''', ''' + @xuser + ''', getutcdate(), getutcdate()'215    END216    IF dbo.QBM_FGIColumnExists(@TableName,217    'XIsInEffect') = 1 AND NOT EXISTS(218    SELECT TOP 1 1219    FROM #QBMBufferTransferOneTable bu220    WHERE221      bu.ColumnName = 'XIsInEffect')222    BEGIN223      SELECT224        @insertlist = @insertlist + ', XIsInEffect'225      SELECT226        @SelectList = @SelectList + ', 1'227    END228    SELECT @insertlist = @insertlist + ')'229    SELECT @cmd = @insertlist + @SelectList + @JoinList + N '230			EndLabel:231			'232    IF @DebugSwitch > 0233    BEGIN234      print substring(@cmd,235      1,236      4000) print substring(@cmd,237      4001,238      4000) print substring(@cmd,239      8001,240      4000) print substring(@cmd,241      12001,242      4000) print substring(@cmd,243      16001,244      4000) print substring(@cmd,245      20001,246      4000)247    END248    EXEC sp_executesql @Vortest249    SELECT @lauf = @@ROWCOUNT250    WHILE @lauf > 0251    BEGIN252      BEGIN TRY253        EXEC sp_executesql @cmd254        SELECT @lauf = @@ROWCOUNT255      END TRY256      BEGIN CATCH257        SELECT @lauf = 0258        EXEC QBM_PSessionErrorAdd DEFAULT,259          @cmd260        RAISERROR('',261        18,262        1)263          WITH NOWAIT264      END CATCH265    END266  END TRY267  BEGIN CATCH268    EXEC QBM_PSessionErrorAdd DEFAULT269    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()270    RAISERROR(@Rethrow,271    18,272    1)273      WITH NOWAIT274  END CATCH275  endLabel:276END
Open raw exported source
SQL · Raw78 lines
1     create   procedure QBM_PBufferT_ProcessInsert ( @ModuleName varchar(3) , @TableName varchar(30)  ) as begin declare @cmd nvarchar(max) declare2 @insertlist nvarchar(max) declare @SelectList nvarchar(max) declare @JoinList nvarchar(max) declare @Vortest nvarchar(max) declare @number int = 0 declare3 @columnname varchar(30) declare @MissingColumnname varchar(30) declare @NumberTableSynonym nvarchar(64) declare @ColumnDatatype nvarchar(64) declare @ColumnDataMaxLen4 int declare @countColumnsInRow int declare @RowsPerShot int declare @lauf int declare @XUser nvarchar(64) = object_name(@@procid) declare @DebugLevel 5char(1) = 'W' declare @DebugSwitch int = 0 declare @columnPattern nvarchar(max) declare @ErrorMessage nvarchar(4000) declare @ErrorSeverity int declare6 @ErrorState int declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int SET XACT_ABORT OFF BEGIN TRY select @countColumnsInRow7 = count(distinct columnname) from #QBMBufferTransferOneTable  if @countColumnsInRow = 0 begin select @cmd = '#LDS#Table {0} not inserted, no Column content delivered|'8 + @TableName + '|' exec QBM_PJournal @cmd, @@procid, 'D', @DebugLevel goto EndLabel end if not exists (select top 1 1 from QBM_VStartupTables su where9 su.TableName = @TableName ) begin select @MissingColumnname = null select top 1 @MissingColumnname = x.ColumnName from ( select distinct bu.ColumnName10 from #QBMBufferTransferOneTable bu  ) as x left outer join ( select columnname from DialogColumn c with (readpast) join DialogTable t with (readpast) 11on c.UID_DialogTable = t.UID_DialogTable where t.TableName = @TableName ) as y on x.ColumnName = y.ColumnName where y.ColumnName is null if @MissingColumnname12 > ' ' begin select @cmd = '#LDS#Table {0} not inserted because of missing ColumnDefinition {1}, processing module {2}|' + @TableName + '|' + @MissingColumnname13 + '|' + @Modulename + '|' exec QBM_PJournal @cmd, @@procid, 'W', @DebugLevel goto EndLabel end end if @DebugSwitch > 0 begin print '@countColumnsInRow'14 + str(@countColumnsInRow) end if @countColumnsInRow < 100  begin select @RowsPerShot = 400000 / @countColumnsInRow end else begin select @RowsPerShot 15= 4000 end  select @columnPattern = N'16		join ( select @ContentClause@ as @columnname@, bux.ObjectkeyOfRow as XObjectKey17					from #QBMBufferTransferOneTable bux 18					where bux.columnname = ''@columnname@''19				) as @number@ on v000.XObjectKey = @number@.XObjectKey'20 select @Vortest = N'21declare @missingList QBM_YParameterList -- t a b l e (ObjectkeyOfRow varchar(138)  -->Parameter12223inser'+'t into @missingList(Parameter1 /*ObjectkeyOfRow*/)24select top '25 + STR(@RowsPerShot) + ' y.ObjectKeyOfRow26 from (27		select ot.ObjectKeyOfRow -- , COUNT(*) as CountItems28			from #QBMBufferTransferOneTable ot 29			group by ot.ObjectKeyOfRow30			having COUNT(*) =  '31 + str(@countColumnsInRow) + '32		) as y	33		where Not exists (select top 1 134						 from ' + @TableName + ' x35						 where x.XObjectKey = y.ObjectkeyOfRow36						)3738'39 select @insertlist = @Vortest + N'40 if @@rowcount = 041  begin42	goto EndLabel43  end	44  					45Inser'+'t into ' + @TableName + ' (XObjectKey' select46 @SelectList = N'47	select v000.XObjectKey ' select @JoinList = N'48from 49 (50	-- bestimmen aller einzufügenden Sätze51	select Parameter1 /*ObjectkeyOfRow*/ as XObjectKey 52		from @missingList53	) as v000'54   if @TableName in (select TableName from QBM_VStartupTables)  begin insert into @ElementBuffer(Ident1) select distinct bu.columnname from QBM_VSchemaColumns55 cc join #QBMBufferTransferOneTable bu on cc.COLUMN_NAME = bu.columnname collate database_default where cc.TABLE_NAME = @TableName  and ( bu.HasContentFull56 = 0 and isnull(bu.ContentShort, '') > ' ' or bu.HasContentFull = 1 and isnull(bu.ContentFull, '') > ' ' ) order by 1 end else begin insert into @ElementBuffer57(Ident1) select distinct bu.columnname from dialogColumn cc with (readpast) join #QBMBufferTransferOneTable bu on cc.COLUMNNAME = bu.columnname join DialogTable58 t with (readpast) on cc.UID_DialogTable = t.UID_DialogTable where t.TableName = @TableName  and ( bu.HasContentFull = 0 and isnull(bu.ContentShort, ''59) > ' ' or bu.HasContentFull = 1 and isnull(bu.ContentFull, '') > ' ' ) order by 1 end select @ElementCount = count(*) from @ElementBuffer select @ElementIndex60 = 1 while @ElementIndex <= @ElementCount begin select top 1 @ColumnName = bu.Ident1 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex select61 @number += 1 select @NumberTableSynonym = 'v' + right('00' + ltrim(str(@number)), 3) if @DebugSwitch > 0 begin print 'Spalte : ' + @columnname end select62 @ColumnDatatype = dbo.QBM_FGIColumnDataType(@TableName , @columnname) select @ColumnDataMaxLen = dbo.QBM_FGIColumnDataLen(@TableName , @columnname) select63 @insertlist = @insertlist + ', ' + @columnname select @SelectList = @SelectList + N'	64			, ' + dbo.QBM_FSQIsNullClauseSet(@TableName , @columnname, @NumberTableSynonym65) select @JoinList = @JoinList + replace(REPLACE(REPLACE(@columnPattern , '@ContentClause@', dbo.QBM_FSQCVBufferToColumn(@TableName , @columnname, N'bux'66, 0)) , '@columnname@', @columnname) , '@number@', @NumberTableSynonym) select @ElementIndex += 1 end  if dbo.QBM_FGIColumnExists(@TableName , 'XUserInserted'67) = 1 begin select @insertlist = @insertlist + ', XuserInserted, XUserUpdated, XDateInserted, XDateupdated' select @SelectList = @SelectList + ', ''' +68 @xuser + ''', ''' + @xuser + ''', getutcdate(), getutcdate()' end   if dbo.QBM_FGIColumnExists(@TableName , 'XIsInEffect') = 1  and not exists (select69 top 1 1 from #QBMBufferTransferOneTable bu where bu.ColumnName = 'XIsInEffect' ) begin select @insertlist = @insertlist + ', XIsInEffect' select @SelectList70 = @SelectList + ', 1' end select @insertlist = @insertlist + ')' select @cmd = @insertlist + @SelectList + @JoinList + N'71			EndLabel:72			' if @DebugSwitch73 > 0 begin print substring(@cmd, 1, 4000) print substring(@cmd, 4001, 4000) print substring(@cmd, 8001, 4000) print substring(@cmd, 12001, 4000) print 74substring(@cmd, 16001, 4000) print substring(@cmd, 20001, 4000) end exec sp_executesql @Vortest select @lauf = @@ROWCOUNT  while @lauf > 0 begin begin 75try exec sp_executesql @cmd select @lauf = @@ROWCOUNT end try begin catch select @lauf = 0 exec QBM_PSessionErrorAdd default, @cmd RAISERROR ('', 18, 176) WITH NOWAIT end catch end    END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() 77RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: end 78