Back to OIM Explorer

dbo.QBM_PViewBuildV_intern

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 3.070 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_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_PSQLCreate source text reference
  • references source dbo.QBM_PViewBuild source text reference
  • references source dbo.QBM_PViewBuildV source text reference
  • references source dbo.QBM_PViewDrop source text reference

References

Referenced By

Complete Source

SQL130 lines
1CREATE PROCEDURE QBM_PViewBuildV_intern(2  @UID_DialogTableV varchar(38),3  @GenProcID varchar(38),4  @ModuleName varchar(3) = '',5  @AutoCorrectColumns BIT = 06)7AS8BEGIN9  DECLARE @viewwhereclause nvarchar(max)10  DECLARE @SQLcmd nvarchar(max)11  DECLARE @cr nvarchar(16) = nchar(13) + nchar(10)12  DECLARE @AnzahlSpalten int = 013  DECLARE @uid_dialogTableBase varchar(38)14  DECLARE @basetablename varchar(39)15  DECLARE @TableNameV varchar(30)16  DECLARE @IsDeactivatedByPreProcessor BIT17  DECLARE @DebugSwitch int = 018  DECLARE @DebugLevel varchar(1) = 'W'19  DECLARE @ErrorMessage nvarchar(4000)20  DECLARE @ErrorSeverity int21  DECLARE @ErrorState int22  SET XACT_ABORT OFF23  BEGIN TRY24    SELECT25      TOP 1 @viewwhereclause = ViewWhereClause,26      @uid_dialogTableBase = UID_DialogTableBase,27      @basetablename = dbo.QBM_FGITableName(UID_DialogTableBase),28      @IsDeactivatedByPreProcessor = t.IsDeactivatedByPreProcessor29    FROM DialogTable t30      WITH(readpast)31    WHERE32      t.UID_DialogTable = @UID_DialogTableV AND EXISTS(33    SELECT TOP 1 134    FROM information_schema.tables tt35    WHERE36      tt.table_name = dbo.QBM_FGITableName(t.UID_DialogTableBase))37    IF @IsDeactivatedByPreProcessor = 138    BEGIN39      SELECT @viewwhereclause = ' 1 = 0 '40    END41    SELECT @TableNameV = dbo.QBM_FGITableName(@UID_DialogTableV)42    IF @DebugSwitch > 043    BEGIN44      print @viewwhereclause45    END46    IF EXISTS(47      SELECT TOP 1 148      FROM information_schema.tables49      WHERE50        table_name = @TableNameV AND table_type = 'VIEW') AND NOT EXISTS(51    SELECT TOP 1 152    FROM DialogTable t53    WHERE54      t.TableName = @TableNameV)55    BEGIN56      EXEC QBM_PViewDrop @TableNameV57    END58    IF NOT EXISTS(59      SELECT TOP 1 160      FROM DialogTable t61      WHERE62        t.TableName = @TableNameV AND t.TableType = 'V')63    BEGIN64      GOTO endLabel65    END66    SELECT67      @SQLcmd = N 'create or alter view dbo.' + @TableNameV + N ' as ' + @cr + N ' select ' + @cr68    SELECT69      @SQLcmd = @SQLcmd + string_agg(convert(nvarchar(max), nchar(9) + b.ColumnName + N ' as ' + v.ColumnName),70      N ',' +nchar(13) + nchar(10))71    FROM dialogcolumn v72      WITH(readpast)73    JOIN DialogColumn b74      WITH(readpast)75      ON v.uid_basecolumn = b.uid_dialogcolumn76    JOIN DialogTable tb77      WITH(readpast)78      ON b.UID_DialogTable = tb.UID_DialogTable79    JOIN DialogTable tv80      WITH(readpast)81      ON v.UID_DialogTable = tv.UID_DialogTable82    JOIN QBM_VSchemaColumns cb83      ON tb.TableName = cb.table_name AND b.columnname = cb.column_name84    WHERE85      tv.UID_DialogTable = @UID_DialogTableV AND tb.UID_DialogTable= @uid_dialogTableBase86    SELECT @AnzahlSpalten = @@ROWCOUNT87    SELECT88      @SQLcmd = CONCAT(@SQLcmd,89      @cr,90      N ' from ',91      @basetablename,92      @cr,93      N ' where ',94      @viewwhereclause,95      @cr)96    IF @DebugSwitch > 097    BEGIN98      print @SQLcmd99    END100    IF @AnzahlSpalten > 0101    BEGIN102      EXEC QBM_PSQLCreate @TableNameV,103      'V',104        @SQLcmd,105        @UnComment = 0106    END107    ELSE108    BEGIN109      EXEC QBM_PViewDrop @TableNameV110    END111    IF @AutoCorrectColumns = 1112    BEGIN113      EXEC QBM_PDialogColumnDelete @TablenameV,114        @GenProcID115      EXEC QBM_PDialogColumnInsert @TablenameV,116        @GenProcID,117        @ModuleName118    END119  END TRY120  BEGIN CATCH121    EXEC QBM_PSessionErrorAdd DEFAULT122    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()123    RAISERROR(@Rethrow,124    18,125    1)126      WITH NOWAIT127  END CATCH128  endLabel:129  RETURN130END
Open raw exported source
SQL ยท Raw21 lines
1   create   procedure QBM_PViewBuildV_intern ( @UID_DialogTableV varchar(38), @GenProcID varchar(38) , @ModuleName varchar(3) = '' , @AutoCorrectColumns2 bit = 0 ) as begin  declare @viewwhereclause nvarchar (max) declare @SQLcmd nvarchar(max) declare @cr nvarchar(16) = nchar(13) + nchar(10) declare @AnzahlSpalten3 int = 0 declare @uid_dialogTableBase varchar(38) declare @basetablename varchar(39) declare @TableNameV varchar(30) declare @IsDeactivatedByPreProcessor4 bit declare @DebugSwitch int = 0 declare @DebugLevel varchar(1) = 'W' declare @ErrorMessage nvarchar(4000) declare @ErrorSeverity int declare @ErrorState5 int  SET XACT_ABORT OFF BEGIN TRY select top 1 @viewwhereclause = ViewWhereClause , @uid_dialogTableBase = UID_DialogTableBase , @basetablename = dbo.QBM_FGITableName6(UID_DialogTableBase) , @IsDeactivatedByPreProcessor = t.IsDeactivatedByPreProcessor from DialogTable t with (readpast) where t.UID_DialogTable = @UID_DialogTableV7  and exists (select top 1 1 from information_schema.tables tt where tt.table_name = dbo.QBM_FGITableName(t.UID_DialogTableBase) ) if @IsDeactivatedByPreProcessor8 = 1 begin select @viewwhereclause = ' 1 = 0 ' end select @TableNameV = dbo.QBM_FGITableName(@UID_DialogTableV)  if @DebugSwitch > 0 begin print @viewwhereclause9 end  if exists (select top 1 1 from information_schema.tables where table_name = @TableNameV and table_type = 'VIEW' ) and not exists (select top 1 1 10from DialogTable t where t.TableName = @TableNameV ) begin exec QBM_PViewDrop @TableNameV end if not exists (select top 1 1 from DialogTable t where t.TableName11 = @TableNameV and t.TableType = 'V' ) begin goto endLabel end select @SQLcmd = N'create or alter view dbo.' + @TableNameV + N' as ' + @cr + N' select '12 + @cr select @SQLcmd = @SQLcmd + string_agg(convert(nvarchar(max), nchar(9) + b.ColumnName + N' as ' + v.ColumnName )  , N',' +nchar(13) + nchar(10) )13  from dialogcolumn v with (readpast) join DialogColumn b with (readpast) on v.uid_basecolumn = b.uid_dialogcolumn join DialogTable tb with (readpast) 14on b.UID_DialogTable = tb.UID_DialogTable join DialogTable tv with (readpast) on v.UID_DialogTable = tv.UID_DialogTable  join QBM_VSchemaColumns cb on 15tb.TableName = cb.table_name and b.columnname = cb.column_name where tv.UID_DialogTable = @UID_DialogTableV  and tb.UID_DialogTable= @uid_dialogTableBase16 select @AnzahlSpalten = @@ROWCOUNT       select @SQLcmd = concat(@SQLcmd , @cr , N' from ' , @basetablename , @cr , N' where ' , @viewwhereclause , @cr17) if @DebugSwitch > 0 begin print @SQLcmd end if @AnzahlSpalten > 0 begin  exec QBM_PSQLCreate @TableNameV, 'V', @SQLcmd, @UnComment = 0 end else begin18  exec QBM_PViewDrop @TableNameV end if @AutoCorrectColumns = 1 begin exec QBM_PDialogColumnDelete @TablenameV, @GenProcID exec QBM_PDialogColumnInsert19 @TablenameV, @GenProcID, @ModuleName end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow20() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: return end 21