Back to OIM Explorer

dbo.QBM_PBufferT_FillColumn

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.825 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_FCVIntToString source text reference
  • references source dbo.QBM_FCVObjectKeyToModuleOwner source text reference
  • references source dbo.QBM_FCVStringToIndent source text reference
  • references source dbo.QBM_FGIColumnDataLen source text reference
  • references source dbo.QBM_FGIColumnDataType source text reference
  • references source dbo.QBM_FGIDBOwner source text reference
  • references source dbo.QBM_FGIPrimaryKeyName source text reference
  • references source dbo.QBM_FGIPrimaryKeyName_F source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_FSQCVColumnToBuffer source text reference
  • references source dbo.QBM_FSQModuleLower source text reference
  • references source dbo.QBM_FSQTableRowOwnedByModule source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL184 lines
1CREATE PROCEDURE QBM_PBufferT_FillColumn(2  @TempTableName nvarchar(64),3  @TargetModule varchar(3),4  @TableName varchar(30),5  @columnname varchar(30),6  @RowCondition nvarchar(max),7  @ModuleName varchar(3) = '#'8)9AS10BEGIN11  DECLARE @datatype nvarchar(64)12  DECLARE @DataMaxLen int13  DECLARE @cmd nvarchar(max)14  DECLARE @SQLInsertlist nvarchar(max)15  DECLARE @SQLSelectListBase nvarchar(max)16  DECLARE @SQLSelectListValues nvarchar(max)17  DECLARE @SQLSelectListNull nvarchar(max)18  DECLARE @FromClause nvarchar(max)19  DECLARE @WhereClause nvarchar(max)20  DECLARE @WhereclauseFK nvarchar(max) = N ''21  DECLARE @WhereclauseNotFK nvarchar(max) = N ''22  DECLARE @DebugLevel char(1) = 'W'23  DECLARE @DebugSwitch int = 024  DECLARE @ErrorMessage nvarchar(4000)25  DECLARE @ErrorSeverity int26  DECLARE @ErrorState int27  DECLARE @TargetFile varchar(1) = 'M'28  SET XACT_ABORT OFF29  BEGIN TRY30    IF @ModuleName = '#'31    BEGIN32      SELECT @ModuleName = dbo.QBM_FGIDBOwner()33    END34    IF @TargetModule = '#'35    BEGIN36      SELECT @TargetModule = @ModuleName37    END38    IF EXISTS(39      SELECT TOP 1 140      FROM QBM_VStartupTables v41      WHERE42        v.TableName = @TableName)43    BEGIN44      SELECT @TargetFile = 'S'45    END46    IF @TableName = 'QBMCustomSQL'47    BEGIN48      SELECT @TargetFile = 'C'49    END50    SELECT51      @DataMaxLen = dbo.QBM_FGIColumnDataLen(@TableName,52      @columnname)53    SELECT54      @datatype = dbo.QBM_FGIColumnDataType(@TableName,55      @columnname)56    IF @DebugSwitch > 057    BEGIN58      print char(9) + char(9) + '-- ' + @TableName + '.' + @columnname + ' ' + @datatype + '(' + dbo.QBM_FCVIntToString(@DataMaxLen) + ')'59    END60    SELECT61      @SQLInsertlist = N 'inser' + 't into ' + @TempTableName + '(TargetFile, moduleName, TableName, columnname, ObjectKeyOfRow, ContentShort, HasContentFull, ContentFull)'62      + NCHAR(13) + NCHAR(10)63    SELECT64      @SQLSelectListBase = N 'select ''@TargetFile@'', ''@ModuleName@'', ''@TableName@'', ''@columnname@'', XObjectKey, '65    SELECT66      @SQLSelectListValues = dbo.QBM_FCVStringToIndent(dbo.QBM_FSQCVColumnToBuffer(@TableName, @columnname,67      @TableName),68      2)69    SELECT70      @SQLSelectListNull = 'null, 0, null'71    SELECT @FromClause = N '72		from @TableName@ '73    SELECT @WhereClause = N '		74		where 1=1'75    IF @RowCondition > ' '76    BEGIN77      SELECT @WhereClause = @WhereClause + N '78			 and ( ' + @RowCondition + ')'79    END80    SELECT81      @WhereClause = @WhereClause + '82		 and ( (' + dbo.QBM_FCVStringToIndent(dbo.QBM_FSQTableRowOwnedByModule(@TableName,83      @ModuleName, ''),84      2) + '85				)86			  or exists (select top 1 1 87							from QBMBufferConfig cf88							where cf.TableName = ''@TableName@''89							and cf.ColumnName = ''@columnname@''90							and cf.ObjectKeyOfRow = @TableName@.XObjectKey 91						)92			)'93    IF @datatype IN('char',94    'nchar',95    'varchar',96    'nvarchar') AND dbo.QBM_FGIPrimaryKeyName_F(@TableName,97    1) <> @columnname AND isnull(dbo.QBM_FGIPrimaryKeyName_F(@TableName, 2),98    '') <> @columnname99    BEGIN100      IF @DebugSwitch > 0101      BEGIN102        print '@DataMaxLen' + str(@DataMaxLen)103      END104      IF @DataMaxLen = 38105      BEGIN106        IF EXISTS(107          SELECT TOP 1 1108          FROM QBM_VQBMRelation cr109          WHERE110            cr.ChildTable = @TableName AND cr.ChildColumn = @columnname)111        BEGIN112          SELECT113            @WhereclauseFK = N '114				 and ( left(isnull(@columnname@, ''''), 4) in ' + dbo.QBM_FSQModuleLower(@ModuleName) + ')'115          SELECT116            @WhereclauseNotFK = N '117				 and ( left(isnull(@columnname@, ''''), 4) not in ' + dbo.QBM_FSQModuleLower(@ModuleName) + ')'118        END119      END120      IF @DataMaxLen = 138121      BEGIN122        IF EXISTS(123          SELECT TOP 1 1124          FROM DialogColumn c125          JOIN DialogTable t126          WITH(readpast)127          ON c.UID_DialogTable = t.UID_DialogTable128        WHERE129          t.TableName = @TableName AND c.ColumnName = @columnname AND c.IsDynamicFK = 1)130        BEGIN131          SELECT132            @WhereclauseFK = N '133				 and ( isnull(dbo.QBM_FCVObjectKeyToModuleOwner(@TableName@.@columnname@), '''') + ''-'' in ' + dbo.QBM_FSQModuleLower134            (@ModuleName) + ')'135          SELECT136            @WhereclauseNotFK = N '137				 and ( isnull(dbo.QBM_FCVObjectKeyToModuleOwner(@TableName@.@columnname@), '''') + ''-'' not in ' + dbo.QBM_FSQModuleLower138            (@ModuleName) + ')'139        END140      END141    END142    SELECT143      @cmd = @SQLInsertlist + @SQLSelectListBase + @SQLSelectListValues + @FromClause + @WhereClause144    IF @WhereclauseFK > ' '145    BEGIN146      SELECT @cmd = @cmd + @WhereclauseFK147      SELECT148        @cmd = @cmd + nchar(13) + nchar(10) + 'union' + nchar(13) + nchar(10) + @SQLSelectListBase + @SQLSelectListNull + @FromClause + @WhereClause149        + @WhereclauseNotFK150    END151    SELECT152      @cmd = replace(replace(replace(REPLACE(@cmd, '@columnname@', @columnname), '@TableName@', @TableName),153      '@ModuleName@', @ModuleName),154      '@TargetFile@',155      @TargetFile)156    IF @DebugSwitch > 0157    BEGIN158      print @cmd159      EXEC QBM_PJournal @Cmd,160        @@procid,161      'D',162        @DebugLevel163    END164    BEGIN TRY165      EXEC sp_executeSQL @cmd166    END TRY167    BEGIN CATCH168      EXEC QBM_PSessionErrorAdd DEFAULT,169        @Cmd170      RAISERROR('',171      18,172      1)173        WITH NOWAIT174    END CATCH175  END TRY176  BEGIN CATCH177    EXEC QBM_PSessionErrorAdd DEFAULT178    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()179    RAISERROR(@Rethrow,180    18,181    1)182      WITH NOWAIT183  END CATCH184END
Open raw exported source
SQL ยท Raw47 lines
1      create   procedure QBM_PBufferT_FillColumn ( @TempTableName nvarchar(64) , @TargetModule varchar(3) , @TableName varchar(30) , @columnname2 varchar(30) , @RowCondition nvarchar(max) , @ModuleName varchar(3) = '#' ) as begin declare @datatype nvarchar(64) declare @DataMaxLen int  declare @cmd3 nvarchar(max) declare @SQLInsertlist nvarchar(max) declare @SQLSelectListBase nvarchar(max) declare @SQLSelectListValues nvarchar(max) declare @SQLSelectListNull4 nvarchar(max) declare @FromClause nvarchar(max) declare @WhereClause nvarchar(max) declare @WhereclauseFK nvarchar(max) = N'' declare @WhereclauseNotFK5 nvarchar(max) = N'' declare @DebugLevel char(1) = 'W' declare @DebugSwitch int = 0 declare @ErrorMessage nvarchar(4000) declare @ErrorSeverity int declare6 @ErrorState int declare @TargetFile varchar(1) = 'M' SET XACT_ABORT OFF BEGIN TRY if @ModuleName = '#' begin select @ModuleName = dbo.QBM_FGIDBOwner()7 end if @TargetModule = '#' begin select @TargetModule = @ModuleName end if exists(select top 1 1 from QBM_VStartupTables v where v.TableName = @TableName8 ) begin select @TargetFile = 'S' end if @TableName = 'QBMCustomSQL' begin select @TargetFile = 'C' end select @DataMaxLen = dbo.QBM_FGIColumnDataLen(@TableName9 , @columnname) select @datatype = dbo.QBM_FGIColumnDataType(@TableName , @columnname)   if @DebugSwitch > 0 begin print char(9) + char(9) + '-- ' + @TableName10 + '.' + @columnname + ' ' + @datatype + '(' + dbo.QBM_FCVIntToString(@DataMaxLen) + ')' end  select @SQLInsertlist = N'inser'+'t into ' + @TempTableName11 + '(TargetFile, moduleName, TableName, columnname, ObjectKeyOfRow, ContentShort, HasContentFull, ContentFull)' + NCHAR(13) + NCHAR(10) select @SQLSelectListBase12 = N'select ''@TargetFile@'', ''@ModuleName@'', ''@TableName@'', ''@columnname@'', XObjectKey, ' select @SQLSelectListValues = dbo.QBM_FCVStringToIndent13 (dbo.QBM_FSQCVColumnToBuffer (@TableName , @columnname, @TableName ), 2) select @SQLSelectListNull = 'null, 0, null' select @FromClause = N'14		from @TableName@ '15 select @WhereClause = N'		16		where 1=1'   if @RowCondition > ' ' begin select @WhereClause = @WhereClause + N'17			 and ( ' + @RowCondition + ')' end18 select @WhereClause = @WhereClause + '19		 and ( (' + dbo.QBM_FCVStringToIndent( dbo.QBM_FSQTableRowOwnedByModule (@TableName , @ModuleName, ''), 2) +20 '21				)22			  or exists (select top 1 1 23							from QBMBufferConfig cf24							where cf.TableName = ''@TableName@''25							and cf.ColumnName = ''@columnname@''26							and cf.ObjectKeyOfRow = @TableName@.XObjectKey 27						)28			)'29 if @datatype in( 'char' , 'nchar', 'varchar', 'nvarchar')  and dbo.QBM_FGIPrimaryKeyName_F(@TableName , 1) <> @columnname and isnull(dbo.QBM_FGIPrimaryKeyName_F30(@TableName , 2), '') <> @columnname begin if @DebugSwitch > 0 begin print '@DataMaxLen' + str(@DataMaxLen) end if @DataMaxLen = 38  begin  if exists (select31 top 1 1 from QBM_VQBMRelation cr where cr.ChildTable = @TableName and cr.ChildColumn = @columnname ) begin select @WhereclauseFK = N'32				 and ( left(isnull(@columnname@, ''''), 4) in '33 + dbo.QBM_FSQModuleLower(@ModuleName) + ')' select @WhereclauseNotFK = N'34				 and ( left(isnull(@columnname@, ''''), 4) not in ' + dbo.QBM_FSQModuleLower35(@ModuleName) + ')' end end if @DataMaxLen = 138  begin  if exists (select top 1 1 from DialogColumn c join DialogTable t with (readpast) on c.UID_DialogTable36 = t.UID_DialogTable where t.TableName = @TableName and c.ColumnName = @columnname and c.IsDynamicFK = 1 ) begin select @WhereclauseFK = N'37				 and ( isnull(dbo.QBM_FCVObjectKeyToModuleOwner(@TableName@.@columnname@), '''') + ''-'' in '38 + dbo.QBM_FSQModuleLower(@ModuleName) + ')' select @WhereclauseNotFK = N'39				 and ( isnull(dbo.QBM_FCVObjectKeyToModuleOwner(@TableName@.@columnname@), '''') + ''-'' not in '40 + dbo.QBM_FSQModuleLower(@ModuleName) + ')' end end end select @cmd = @SQLInsertlist + @SQLSelectListBase + @SQLSelectListValues + @FromClause + @WhereClause41 if @WhereclauseFK > ' ' begin select @cmd = @cmd + @WhereclauseFK select @cmd = @cmd + nchar(13) + nchar(10) +'union' + nchar(13) + nchar(10) + @SQLSelectListBase42 + @SQLSelectListNull + @FromClause + @WhereClause + @WhereclauseNotFK end  select @cmd = replace(replace(replace(REPLACE(@cmd, '@columnname@', @columnname43) , '@TableName@', @TableName ) , '@ModuleName@', @ModuleName) , '@TargetFile@', @TargetFile) if @DebugSwitch > 0 begin print @cmd exec QBM_PJournal @Cmd44, @@procid, 'D', @DebugLevel end begin try exec sp_executeSQL @cmd end try begin catch exec QBM_PSessionErrorAdd default, @Cmd RAISERROR ('', 18, 1) WITH45 NOWAIT end catch END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow46, 18, 1) WITH NOWAIT END CATCH end 47