Back to OIM Explorer

dbo.QBM_PViewBuildP_intern

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.238 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_FCVElementToObjectKey1 source text reference
  • references source dbo.QBM_FCVGuidToTransfer source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_FGITableName source text reference
  • references source dbo.QBM_PDialogColumnDelete source text reference
  • references source dbo.QBM_PDialogColumnInsert source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PViewBuild source text reference
  • references source dbo.QBM_PViewBuild_FromAddOn source text reference
  • references source dbo.QBM_PViewBuildP source text reference
  • references source dbo.QBM_PViewDrop source text reference

References

Referenced By

Complete Source

SQL195 lines
1CREATE PROCEDURE QBM_PViewBuildP_intern(2  @UID_DialogTableP varchar(38),3  @GenProcID varchar(38),4  @ModuleName varchar(3) = '',5  @AutoCorrectColumns BIT = 06)7AS8BEGIN9  DECLARE @SQLcmd nvarchar(max)10  DECLARE @queryAdd nvarchar(max)11  DECLARE @UID_TableBase varchar(38)12  DECLARE @TableNameBase varchar(30)13  DECLARE @QueryWork nvarchar(max)14  DECLARE @UID_QBMViewAddOn varchar(38)15  DECLARE @Ident_QBMViewAddOn nvarchar(64)16  DECLARE @TableNameP varchar(30)17  DECLARE @DebugSwitch int = 018  DECLARE @WhereClauseExcludeTable nvarchar(max)19  DECLARE @DebugLevel varchar(1) = 'W'20  DECLARE @ElementLast int21  DECLARE @ElementBufferMulti QBM_YCursorBuffer22  DECLARE @ElementCount int23  DECLARE @ElementIndex int24  SET XACT_ABORT OFF25  BEGIN TRY26    SELECT @TableNameP = dbo.QBM_FGITableName(@UID_DialogTableP)27    IF @DebugSwitch > 028    BEGIN29      print '@TableNameP ' + @TableNameP30    END31    IF EXISTS(32      SELECT TOP 1 133      FROM information_schema.tables34      WHERE35        table_name = @TableNameP AND table_type = 'VIEW') AND NOT EXISTS(36    SELECT TOP 1 137    FROM dialogtable t38    WHERE39      t.TableName = @TableNameP)40    BEGIN41      EXEC QBM_PViewDrop @TableNameP42    END43    IF NOT EXISTS(44      SELECT TOP 1 145      FROM DialogTable t46      WHERE47        t.TableName = @TableNameP AND t.TableType = 'P')48    BEGIN49      GOTO endLabel50    END51    IF NOT EXISTS(52      SELECT TOP 1 153      FROM DialogTable t54      WHERE55        t.UID_DialogTable = @UID_DialogTableP) OR NOT EXISTS(56    SELECT TOP 1 157    FROM DialogTable t58    WHERE59      t.UID_DialogTableUnion = @UID_DialogTableP)60    BEGIN61      GOTO endLabel62    END63    DELETE QBMViewAddOn64    WHERE65      uid_Dialogtable = @UID_DialogTableP AND IsGenerated = 166    INSERT INTO @ElementBufferMulti(UID1,67    Ident1,68    ContentFull,69    ContentShort)70    SELECT71      t.UID_DialogTable,72      t.TableName,73      t.ExtensionForProxyTable,74    CASE t.IsDeactivatedByPreprocessor75      WHEN 1 THEN76    ' where ( 1= 0) -- table deactivated '77    ELSE ''78    END AS WhereClauseExcludeTable79    FROM DialogTable t80    WHERE81      t.UID_DialogTableUnion = @UID_DialogTableP82    SELECT @ElementCount = @@ROWCOUNT83    SELECT @ElementIndex = @@IDENTITY - @ElementCount +184    SELECT @ElementLast = @@IDENTITY85    WHILE @ElementIndex <= @ElementLast86    BEGIN87      SELECT88        TOP 1 @UID_TableBase = bu.UID1,89        @TableNameBase = bu.Ident1,90        @queryAdd = bu.ContentFull,91        @WhereClauseExcludeTable = bu.ContentShort92      FROM @ElementBufferMulti bu93      WHERE94        bu.ElementIndex = @ElementIndex95      SELECT @Ident_QBMViewAddOn =96      LEFT(CONCAT(@TableNameP, ' : ', @TableNameBase),97      64)98      IF @DebugSwitch > 099      BEGIN100        print '@UID_TableBase ' + @UID_TableBase101      END102      SELECT103        @QueryWork = string_agg(convert(nvarchar(max), b.ColumnName + ' as ' + p.ColumnName),104        N ', ')105      FROM DialogColumn b106        WITH(readpast)107      JOIN DialogColumn p108        WITH(readpast)109        ON b.UID_DialogColumnUnionView = p.UID_DialogColumn110      WHERE111        b.UID_DialogTable = @UID_TableBase AND p.UID_DialogTable = @UID_DialogTableP112      IF @queryAdd > ' '113      BEGIN114        SELECT115          @QueryWork = @QueryWork + ', ' + @queryAdd116      END117      IF @DebugSwitch > 0118      BEGIN119        print 'len @QueryWork ' + str(len(@QueryWork)) print '@QueryWork ' + @QueryWork + '#'120      END121      IF @QueryWork > ' '122      BEGIN123        SELECT124          @QueryWork = CONCAT('select ',125          @QueryWork,126          '127					from ',128          @TableNameBase,129          '130					',131          @WhereClauseExcludeTable)132        SELECT133          @UID_QBMViewAddOn = dbo.QBM_FCVGuidToTransfer(NEWID(),134          'ZZZ')135        INSERT INTO QBMViewAddOn(UID_QBMViewAddOn,136        UID_DialogTable,137        QueryString,138        Ident_QBMViewAddOn,139        IsGenerated,140        XDateInserted,141        XDateUpdated,142        XUserInserted,143        XUserUpdated,144        XObjectKey)145        SELECT146          @UID_QBMViewAddOn,147          @UID_DialogTableP,148          @QueryWork,149          @Ident_QBMViewAddOn,150          1,151          GETUTCDATE(),152          GETUTCDATE(),153          OBJECT_NAME(@@PROCID),154          OBJECT_NAME(@@PROCID),155          dbo.QBM_FCVElementToObjectKey1('QBMViewAddOn',156          'UID_QBMViewAddOn',157          @UID_QBMViewAddOn)158      END159      ELSE160      BEGIN161        IF @DebugSwitch > 0162        BEGIN163          print 'Tabelle ohne aufzubauende Spalten ' + @UID_TableBase164        END165      END166      SELECT @ElementIndex += 1167    END168    IF @DebugSwitch > 0169    BEGIN170      SELECT *171      FROM QBMViewAddOn172      WHERE173        UID_DialogTable = @UID_DialogTableP174    END175    EXEC QBM_PViewBuild_FromAddOn @uid_dialogtableP176    IF @AutoCorrectColumns = 1177    BEGIN178      EXEC QBM_PDialogColumnDelete @TableNameP,179        @GenProcID180      EXEC QBM_PDialogColumnInsert @TableNameP,181        @GenProcID,182        @ModuleName183    END184  END TRY185  BEGIN CATCH186    EXEC QBM_PSessionErrorAdd DEFAULT187    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()188    RAISERROR(@Rethrow,189    18,190    1)191      WITH NOWAIT192  END CATCH193  endLabel:194  RETURN195END
Open raw exported source
SQL ยท Raw31 lines
1         create   procedure QBM_PViewBuildP_intern ( @UID_DialogTableP varchar(38), @GenProcID varchar(38) , @ModuleName varchar(3) = '' , @AutoCorrectColumns2 bit = 0 ) as begin declare @SQLcmd nvarchar(max) declare @queryAdd nvarchar(max) declare @UID_TableBase varchar(38) declare @TableNameBase varchar(30)3 declare @QueryWork nvarchar(max) declare @UID_QBMViewAddOn varchar(38) declare @Ident_QBMViewAddOn nvarchar(64) declare @TableNameP varchar(30) declare4 @DebugSwitch int = 0 declare @WhereClauseExcludeTable nvarchar(max) declare @DebugLevel varchar(1) = 'W' declare @ElementLast int declare @ElementBufferMulti5 QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int SET XACT_ABORT OFF BEGIN TRY select @TableNameP = dbo.QBM_FGITableName(@UID_DialogTableP6) IF @DebugSwitch > 0 begin print '@TableNameP ' + @TableNameP end if exists (select top 1 1 from information_schema.tables where table_name = @TableNameP7 and table_type = 'VIEW' ) and not exists (select top 1 1 from dialogtable t where t.TableName = @TableNameP ) begin exec QBM_PViewDrop @TableNameP end8 if not exists (select top 1 1 from DialogTable t where t.TableName = @TableNameP and t.TableType = 'P' ) begin goto endLabel end  if not exists (select9 top 1 1 from DialogTable t where t.UID_DialogTable = @UID_DialogTableP )  or not exists (select top 1 1 from DialogTable t where t.UID_DialogTableUnion10 = @UID_DialogTableP ) begin goto endLabel end delete QBMViewAddOn where uid_Dialogtable = @UID_DialogTableP and IsGenerated = 1   insert into @ElementBufferMulti11 (UID1  , Ident1  , ContentFull, ContentShort) select t.UID_DialogTable, t.TableName, t.ExtensionForProxyTable  , case t.IsDeactivatedByPreprocessor when12 1 then ' where ( 1= 0) -- table deactivated ' else '' end as WhereClauseExcludeTable from DialogTable t where t.UID_DialogTableUnion = @UID_DialogTableP13 select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast14 begin select top 1 @UID_TableBase = bu.UID1 , @TableNameBase = bu.Ident1 , @queryAdd = bu.ContentFull , @WhereClauseExcludeTable = bu.ContentShort from15 @ElementBufferMulti bu where bu.ElementIndex = @ElementIndex select @Ident_QBMViewAddOn = left(concat( @TableNameP , ' : ' , @TableNameBase ) , 64) if16 @DebugSwitch > 0 begin print '@UID_TableBase ' + @UID_TableBase end select @QueryWork = string_agg( convert(nvarchar(max), b.ColumnName + ' as ' + p.ColumnName17 )  , N', ' )  from DialogColumn b with (readpast) join DialogColumn p with (readpast) on b.UID_DialogColumnUnionView = p.UID_DialogColumn where b.UID_DialogTable18 = @UID_TableBase  and p.UID_DialogTable = @UID_DialogTableP if @queryAdd > ' ' begin select @QueryWork = @QueryWork + ', ' + @queryAdd end if @DebugSwitch19 > 0 begin print 'len @QueryWork ' + str(len(@QueryWork)) print '@QueryWork ' + @QueryWork + '#' end  if @QueryWork > ' ' begin select @QueryWork = concat20('select ' , @QueryWork, '21					from ' , @TableNameBase , '22					' , @WhereClauseExcludeTable )   select @UID_QBMViewAddOn = dbo.QBM_FCVGuidToTransfer23( NEWID(), 'ZZZ') insert into QBMViewAddOn ( UID_QBMViewAddOn, UID_DialogTable, QueryString , Ident_QBMViewAddOn , IsGenerated , XDateInserted, XDateUpdated24, XUserInserted, XUserUpdated , XObjectKey ) select @UID_QBMViewAddOn, @UID_DialogTableP, @QueryWork , @Ident_QBMViewAddOn , 1  , GETUTCDATE(), GETUTCDATE25(), OBJECT_NAME(@@PROCID), OBJECT_NAME(@@PROCID) , dbo.QBM_FCVElementToObjectKey1('QBMViewAddOn', 'UID_QBMViewAddOn', @UID_QBMViewAddOn) end  else begin26 if @DebugSwitch > 0 begin print 'Tabelle ohne aufzubauende Spalten ' + @UID_TableBase end end select @ElementIndex += 1 end  if @DebugSwitch > 0 begin27 select * from QBMViewAddOn where UID_DialogTable = @UID_DialogTableP end  exec QBM_PViewBuild_FromAddOn @uid_dialogtableP if @AutoCorrectColumns = 1 begin28 exec QBM_PDialogColumnDelete @TableNameP , @GenProcID exec QBM_PDialogColumnInsert @TableNameP , @GenProcID, @ModuleName end END TRY BEGIN CATCH exec 29QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel:30 return end 31