Back to OIM Explorer

dbo.QBM_PBufferT_FillTable_CCC

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 3.298 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_FCVStringToIndent source text reference
  • references source dbo.QBM_FGIColumnExists source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PBufferT_FillColumn source text reference
  • references source dbo.QBM_PBufferT_FillColumn_CCC source text reference
  • references source dbo.QBM_PBufferT_FillTable source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL155 lines
1CREATE PROCEDURE QBM_PBufferT_FillTable_CCC(2  @TableName varchar(30),3  @TempTableName varchar(30)4)5AS6BEGIN7  DECLARE @CmdColumnSelect nvarchar(max)8  DECLARE @columnname varchar(30)9  DECLARE @RowCondition nvarchar(max)10  DECLARE @RowCondition_Table nvarchar(max)11  DECLARE @ColumnNames QBM_YCursorBuffer12  DECLARE @ElementCount int13  DECLARE @ElementIndex int14  DECLARE @DebugSwitch int = 015  DECLARE @PKName1 varchar(30),16  @PKName2 varchar(30)17  DECLARE @OwnerClause nvarchar(max)18  SET XACT_ABORT OFF19  BEGIN TRY20    SELECT21      TOP 1 @PKName1 = isnull(t.PKName1,22      ''),23      @PKName2 = isnull(t.PKName2,24      '')25    FROM DialogTable t26      WITH(readpast)27    WHERE28      t.TableName = @TableName29    SELECT @RowCondition_Table = NULL30    SELECT TOP 1 @RowCondition_Table = t.TransportWhereClause31    FROM DialogTable t32      WITH(readpast)33    WHERE34      t.TableName = @TableName AND t.TransportWhereClause > ' '35    SELECT @RowCondition = '(1=1' + CASE36    WHEN dbo.QBM_FGIColumnExists(@TableName,37    'XOrigin') = 1 THEN38    ' and ( XOrigin > 0)'39    ELSE ''40    END + CASE41    WHEN @RowCondition_Table > ' ' THEN42    ' and (' + @RowCondition_Table + ')'43    ELSE ''44    END + ')'45    IF @DebugSwitch > 046    BEGIN47      print @RowCondition48    END49    IF @RowCondition = '(1=1)'50    BEGIN51      SELECT @RowCondition = ''52    END53    IF isnull(@PKName2,54    '') = ''55    BEGIN56      SELECT @OwnerClause = ' o.' + @PKName1 + ' like ''CCC-%'' '57    END58    ELSE59    BEGIN60      SELECT61        @OwnerClause = CONCAT('( (o.',62        @PKName1,63        ' like ''CCC-%''64	and o.',65        @PKName2,66        ' like ''[a-z][0-9,a-z][0-9,a-z]-%''67   )68	or  69  (o.',70        @PKName2,71        ' like ''CCC-%''72	and o.',73        @PKName1,74        ' like ''[a-z][0-9,a-z][0-9,a-z]-%''75  )76)77')78    END79    SELECT80      @CmdColumnSelect = CONCAT('81  select distinct c.ColumnName82	from DialogColumn c join QBM_VSchemaColumns cc on c.columnname = cc.Column_name83						join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable84															and t.TableName = cc.table_name85	where t.TableName = '''86      ,87      @TableName,88      '''89	 and c.columnname not in (''XObjectKey''90								, ''Xtouched''91								, ''Xdateinserted'', ''Xdateupdated''92								, ''XUserInserted'', ''XUserUpdated''93								, ''XMarkedForDeletion''94--								, ''XOrigin''95								, ''XIsInEffect''96	 )97	and ( c.IsNoTransfer = 098		)99	'100      ,101    CASE @TableName102      WHEN 'QBMNonLinearDepend' THEN103    ''104    ELSE '105	 and ( exists (select top 1 1 106						from ' + @TableName + ' o with (readpast)107						where 108' + dbo.QBM_FCVStringToIndent(@OwnerClause109    , 6) + '110					)111		 )'112    END,113    '114	order by c.ColumnName')115    IF @DebugSwitch > 0116    BEGIN117      print @CmdColumnSelect118    END119    INSERT INTO @ColumnNames(Ident1)120    EXEC sp_executesql @CmdColumnSelect121    SELECT @ElementCount = @@ROWCOUNT122    IF @DebugSwitch > 0123    BEGIN124      SELECT Ident1125      FROM @ColumnNames126    END127    SELECT @ElementIndex = 1128    WHILE @ElementIndex <= @ElementCount129    BEGIN130      SELECT TOP 1 @columnname = bu.Ident1131      FROM @ColumnNames bu132      WHERE133        bu.ElementIndex = @ElementIndex134      IF @DebugSwitch > 0135      BEGIN136        print char(9) + 'Dumping column ' + @columnname + ' rows: ' + @rowcondition137      END138      EXEC QBM_PBufferT_FillColumn_CCC @TableName,139        @columnname,140        @rowcondition = @RowCondition,141        @TempTableName = @TempTableName142      SELECT @ElementIndex += 1143    END144  END TRY145  BEGIN CATCH146    EXEC QBM_PSessionErrorAdd DEFAULT147    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()148    RAISERROR(@Rethrow,149    18,150    1)151      WITH NOWAIT152  END CATCH153  endLabel:154  RETURN155END
Open raw exported source
SQL ยท Raw50 lines
1      create   procedure QBM_PBufferT_FillTable_CCC ( @TableName varchar(30) , @TempTableName varchar(30)  ) as begin declare @CmdColumnSelect nvarchar2(max) declare @columnname varchar(30) declare @RowCondition nvarchar(max) declare @RowCondition_Table nvarchar(max) declare @ColumnNames QBM_YCursorBuffer3 declare @ElementCount int declare @ElementIndex int declare @DebugSwitch int = 0 declare @PKName1 varchar(30) , @PKName2 varchar(30) declare @OwnerClause4 nvarchar(max) SET XACT_ABORT OFF BEGIN TRY select top 1 @PKName1 = isnull(t.PKName1, '') , @PKName2 = isnull(t.PKName2, '') from DialogTable t with (readpast5) where t.TableName = @TableName select @RowCondition_Table = null select top 1 @RowCondition_Table = t.TransportWhereClause from DialogTable t with (readpast6) where t.TableName = @TableName and t.TransportWhereClause > ' ' select @RowCondition = '(1=1' + case when dbo.QBM_FGIColumnExists(@TableName, 'XOrigin'7) = 1 then ' and ( XOrigin > 0)' else '' end + case when @RowCondition_Table > ' ' then ' and (' + @RowCondition_Table + ')' else '' end + ')' if @DebugSwitch8 > 0 begin print @RowCondition end if @RowCondition = '(1=1)' begin select @RowCondition = '' end  if isnull(@PKName2, '') = '' begin select @OwnerClause9 = ' o.' + @PKName1 + ' like ''CCC-%'' ' end else  begin select @OwnerClause = concat( '( (o.' , @PKName1 , ' like ''CCC-%''10	and o.' , @PKName2 , ' like ''[a-z][0-9,a-z][0-9,a-z]-%''11   )12	or  13  (o.'14 , @PKName2 , ' like ''CCC-%''15	and o.' , @PKName1 , ' like ''[a-z][0-9,a-z][0-9,a-z]-%''16  )17)18' )  end select @CmdColumnSelect = concat('19  select distinct c.ColumnName20	from DialogColumn c join QBM_VSchemaColumns cc on c.columnname = cc.Column_name21						join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable22															and t.TableName = cc.table_name23	where t.TableName = '''24 , @TableName , '''25	 and c.columnname not in (''XObjectKey''26								, ''Xtouched''27								, ''Xdateinserted'', ''Xdateupdated''28								, ''XUserInserted'', ''XUserUpdated''29								, ''XMarkedForDeletion''30--								, ''XOrigin''31								, ''XIsInEffect''32	 )33	and ( c.IsNoTransfer = 034		)35	'36 , case @TableName when 'QBMNonLinearDepend' then '' else '37	 and ( exists (select top 1 1 38						from ' + @TableName + ' o with (readpast)39						where 40'41 + dbo.QBM_FCVStringToIndent( @OwnerClause, 6) + '42					)43		 )' end , '44	order by c.ColumnName' )  if @DebugSwitch > 0 begin print @CmdColumnSelect 45end insert into @ColumnNames(Ident1 ) exec sp_executesql @CmdColumnSelect select @ElementCount = @@ROWCOUNT if @DebugSwitch > 0 begin select Ident1 from46 @ColumnNames end select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @columnname = bu.Ident1  from @ColumnNames bu where 47bu.ElementIndex = @ElementIndex  if @DebugSwitch > 0 begin print char(9) + 'Dumping column ' + @columnname + ' rows: ' + @rowcondition end exec QBM_PBufferT_FillColumn_CCC48 @TableName , @columnname , @rowcondition = @RowCondition , @TempTableName = @TempTableName select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd49 default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: return end 50