Back to OIM Explorer

dbo.QBM_PViewBuild_FromAddOn

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 8.399 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_FCVStringTrimRight source text reference
  • references source dbo.QBM_FGICodeName source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PJournal 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_PViewDrop source text reference

References

Referenced By

Complete Source

SQL361 lines
1CREATE PROCEDURE QBM_PViewBuild_FromAddOn(2  @uid_DialogTableTarget varchar(38)3)4AS5BEGIN6  DECLARE @QueryString nvarchar(max)7  DECLARE @UID_QBMViewAddOn varchar(38)8  DECLARE @Cmd nvarchar(max)9  DECLARE @Gesamt nvarchar(max)10  DECLARE @SelectLine nvarchar(max) = ''11  DECLARE @SelectSingleUnionPart nvarchar(max) = ''12  DECLARE @Maxlen int13  DECLARE @ColumnName varchar(30)14  DECLARE @Datatype nvarchar(64)15  DECLARE @Ident_QBMViewAddOn nvarchar(64)16  DECLARE @Viewname varchar(30)17  DECLARE @viewWhereClause nvarchar(max)18  DECLARE @IsDeactivatedByPreProcessor BIT19  DECLARE @MyTempViewName nvarchar(100)20  DECLARE @Columns TABLE(UID_QBMViewAddOn varchar(38) collate database_default,21  columnName varchar(38) collate database_default,22  DATA_TYPE nvarchar(64) collate database_default,23  CHARACTER_MAXIMUM_LENGTH int)24  DECLARE @Datatypes TABLE(Datatype nvarchar(64) collate database_default,25  SortOrder int)26  DECLARE @debuglevel nvarchar(1) = 'W'27  DECLARE @DebugMessage nvarchar(max)28  DECLARE @ErrorMessage nvarchar(4000)29  DECLARE @ErrorSeverity int30  DECLARE @ErrorState int31  DECLARE @SynonymIntern nvarchar(16) = 'xxTab'32  DECLARE @DebugSwitch int = 033  DECLARE @ElementLast int34  DECLARE @ElementBufferMulti QBM_YCursorBuffer35  DECLARE @ElementCount int36  DECLARE @ElementIndex int37  DECLARE @ElementLast_c4 int38  DECLARE @ElementBufferMulti_c4 QBM_YCursorBuffer39  DECLARE @ElementCount_c4 int40  DECLARE @ElementIndex_c4 int41  SET XACT_ABORT OFF42  BEGIN TRY43    INSERT INTO @Datatypes(Datatype,44    SortOrder)45    VALUES('nvarchar',46    0),47    ('varchar',48    100),49    ('nchar',50    200),51    ('char',52    300),53    ('varbinary',54    1000),55    ('binary',56    1100),57    ('float',58    2001),59    ('datetime',60    2100),61    ('numeric',62    3001),63    ('bigint',64    3101),65    ('int',66    3201),67    ('smallint',68    3301),69    ('tinyint',70    3401),71    ('bit',72    3501)73    SELECT74      TOP 1 @Viewname = t.TableName,75      @viewWhereClause = dbo.QBM_FCVStringTrimRight(t.ViewWhereClause,76      ' ' +CHAR(9) + char(13) + char(10),77      0),78      @IsDeactivatedByPreProcessor = t.IsDeactivatedByPreProcessor79    FROM DialogTable t80      WITH(readpast)81    WHERE82      t.UID_DialogTable = @uid_DialogTableTarget83    SELECT @MyTempViewName =84    LEFT(CONCAT('TST_FTemporaryElement', reverse(dbo.QBM_FGICodeName('V', NEWID()))),85    30)86    IF @IsDeactivatedByPreProcessor = 187    BEGIN88      SELECT @viewWhereClause = ' 1 = 0 '89    END90    IF @DebugSwitch > 091    BEGIN92      print '@Viewname ' + isnull(@Viewname,93      '<none>') print '@viewWhereClause ' + isnull(@viewWhereClause,94      '<none>')95    END96    DELETE @ElementBufferMulti97    INSERT INTO @ElementBufferMulti(UID1,98    ContentFull)99    SELECT100      a.uid_QBMViewAddOn,101      a.QueryString102    FROM QBMViewAddOn a103    LEFT104    OUTER105    JOIN QBMModuleDef m106      ON a.UID_QBMViewAddOn LIKE '___-%' AND107    LEFT(a.UID_QBMViewAddOn,108    3) = m.ModuleName109    WHERE110      a.uid_dialogtable = @uid_DialogTableTarget111    ORDER BY ISNULL(m.SortOrder,112    POWER(2, 30)) ASC113    SELECT @ElementCount = @@ROWCOUNT114    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1115    SELECT @ElementLast = @@IDENTITY116    WHILE @ElementIndex <= @ElementLast117    BEGIN118      SELECT119        TOP 1 @UID_QBMViewAddOn = bu.UID1,120        @QueryString = bu.ContentFull121      FROM @ElementBufferMulti bu122      WHERE123        bu.ElementIndex = @ElementIndex124      IF @DebugSwitch > 0125      BEGIN126        print @UID_QBMViewAddOn print @QueryString127      END128      EXEC QBM_PViewDrop @MyTempViewName129      SELECT @Cmd = 'create or alter view ' + @MyTempViewName + ' as ' + @QueryString130      EXEC sp_executesql @cmd131      INSERT INTO @Columns(UID_QBMViewAddOn,132      columnName,133      DATA_TYPE,134      CHARACTER_MAXIMUM_LENGTH)135      SELECT136        @UID_QBMViewAddOn,137        LEFT(c.COLUMN_NAME,138        30),139        c.DATA_TYPE,140      CASE isnull(c.CHARACTER_MAXIMUM_LENGTH,141      0)142        WHEN -1 THEN143      POWER(2,144      30)145        WHEN 0 THEN146      10147      ELSE c.CHARACTER_MAXIMUM_LENGTH148      END149      FROM INFORMATION_SCHEMA.VIEWS v150      JOIN INFORMATION_SCHEMA.COLUMNS c151        ON v.TABLE_NAME = c.TABLE_NAME152      WHERE153        v.TABLE_NAME = @MyTempViewName154      SELECT @ElementIndex += 1155    END156    EXEC QBM_PViewDrop @MyTempViewName157    IF NOT EXISTS(158      SELECT TOP 1 1159      FROM @Columns)160    BEGIN161      SELECT @debugmessage = 'no definition(s) found for View ' + @Viewname162      IF @DebugSwitch > 0 print @debugmessage163      EXEC QBM_PJournal @debugmessage,164        @@procid,165      'W',166        @debuglevel167      EXEC QBM_PViewdrop @Viewname168      GOTO endLabel169    END170    SELECT @SelectLine = ''171    SELECT @SelectSingleUnionPart = ''172    DELETE @ElementBufferMulti173    INSERT INTO @ElementBufferMulti(Ident1,174    Int1)175    SELECT176      c.columnName,177      MAX(CHARACTER_MAXIMUM_LENGTH)178    FROM @Columns c179    GROUP BY c.columnName180    ORDER BY c.columnName181    SELECT @ElementCount = @@ROWCOUNT182    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1183    SELECT @ElementLast = @@IDENTITY184    WHILE @ElementIndex <= @ElementLast185    BEGIN186      SELECT187        TOP 1 @columnname = bu.Ident1,188        @maxlen = bu.Int1189      FROM @ElementBufferMulti bu190      WHERE191        bu.ElementIndex = @ElementIndex192      SELECT TOP 1 @Datatype = c.DATA_TYPE193      FROM @Columns c194      LEFT195      OUTER196      JOIN @Datatypes AS w197        ON c.DATA_TYPE = w.datatype198      WHERE199        c.columnName = @ColumnName200      ORDER BY w.SortOrder ASC201      SELECT202        @SelectLine = @SelectLine + ', ' + @ColumnName203      SELECT204        @SelectSingleUnionPart = @SelectSingleUnionPart + ', convert(' + @Datatype + CASE205        WHEN @Datatype LIKE '%char%' OR @Datatype LIKE '%binary%' THEN206      CASE @Maxlen207        WHEN POWER(2,208      30) THEN209      '(max)'210      ELSE '(' + ltrim(STR(@maxlen)) + ')'211      END212      ELSE ''213      END + ', null) as ' + @ColumnName214      SELECT @ElementIndex += 1215    END216    IF @DebugSwitch > 0217    BEGIN218      print '@SelectLine ' + isnull(@SelectLine,219      '<none>') print '@SelectSingleUnionPart ' + isnull(@SelectSingleUnionPart,220      '<none>')221    END222    SELECT223      @Gesamt = 'create or alter view dbo.' + @Viewname + ' as 224 select * from 225  (226	select ' + SUBSTRING(@SelectSingleUnionPart,227      2,228      LEN(@SelectSingleUnionPart) -1) + '229	 where 1=0 '230    DECLARE @ColumnEmpty varchar(30)231    DELETE @ElementBufferMulti232    INSERT INTO @ElementBufferMulti(UID1,233    ContentFull,234    LongIdent1)235    SELECT236      a.uid_QBMViewAddOn,237      a.QueryString,238      a.Ident_QBMViewAddOn239    FROM QBMViewAddOn a240    WHERE241      a.uid_dialogtable = @uid_DialogTableTarget242    SELECT @ElementCount = @@ROWCOUNT243    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1244    SELECT @ElementLast = @@IDENTITY245    WHILE @ElementIndex <= @ElementLast246    BEGIN247      SELECT248        TOP 1 @UID_QBMViewAddOn = bu.UID1,249        @querystring = bu.ContentFull,250        @Ident_QBMViewAddOn = bu.LongIdent1251      FROM @ElementBufferMulti bu252      WHERE253        bu.ElementIndex = @ElementIndex254      SELECT @SelectSingleUnionPart = ''255      DELETE @ElementBufferMulti_c4256      INSERT INTO @ElementBufferMulti_c4(Ident1,257      Ident2)258      SELECT259        x.columnName,260        y.columnName261      FROM(262      SELECT263        DISTINCT c.columnName264      FROM @Columns c) AS x265      LEFT266      OUTER267      JOIN(268      SELECT ct.columnName269      FROM @Columns ct270      WHERE271        ct.UID_QBMViewAddOn = @UID_QBMViewAddOn) AS y272        ON x.columnName = y.columnName273      ORDER BY x.columnName274      SELECT @ElementCount_c4 = @@ROWCOUNT275      SELECT @ElementIndex_c4 = @@IDENTITY - @ElementCount_c4 +1276      SELECT @ElementLast_c4 = @@IDENTITY277      WHILE @ElementIndex_c4 <= @ElementLast_c4278      BEGIN279        SELECT280          TOP 1 @columnname = bu.Ident1,281          @columnempty = bu.Ident2282        FROM @ElementBufferMulti_c4 bu283        WHERE284          bu.ElementIndex = @ElementIndex_c4285        SELECT286          @SelectSingleUnionPart = @SelectSingleUnionPart + ', ' + CASE287          WHEN @columnempty IS NULL AND EXISTS(288        SELECT TOP 1 1289        FROM @Columns c290        JOIN @Datatypes t291          ON c.DATA_TYPE = t.Datatype292        WHERE293          c.columnName = @ColumnName AND t.SortOrder % 2 = 1) THEN294        ' convert(bit, 0) as '295        WHEN @columnempty IS NULL THEN296        ' null as '297        ELSE @SynonymIntern + '.'298        END + @ColumnName299        SELECT @ElementIndex_c4 += 1300      END301      SELECT302        @Gesamt = @Gesamt + '303	union all304	-- part from ' + isnull(@Ident_QBMViewAddOn,305        @UID_QBMViewAddOn) + '306	select ' + SUBSTRING(@SelectSingleUnionPart,307        2,308        len(@SelectSingleUnionPart) -1) + '309	 from (310' + dbo.QBM_FCVStringToIndent(@querystring,311        3) + '312		) as ' + @SynonymIntern313      IF @DebugSwitch > 0314      BEGIN315        print '------------'316        SELECT317          @DebugMessage = 'select ' + SUBSTRING(@SelectSingleUnionPart,318          2,319          len(@SelectSingleUnionPart) -1) + '320			 from (321		' + dbo.QBM_FCVStringToIndent(@querystring,322          3) + '323				) as ' + @SynonymIntern print @DebugMessage324      END325      SELECT @ElementIndex += 1326    END327    SELECT @Gesamt = @Gesamt + '328 ) as x'329    IF @viewWhereClause > ' ' AND NOT EXISTS(330      SELECT TOP 1 1331      FROM QBMViewAddOn a332      WHERE333        a.UID_DialogTable = @uid_DialogTableTarget AND a.QueryString = @viewWhereClause)334    BEGIN335      SELECT336        @Gesamt = @Gesamt + '337 where (338 ' + dbo.QBM_FCVStringToIndent(@viewWhereClause,339        2) + '340		)'341    END342    IF @DebugSwitch > 0343    BEGIN344      SELECT @gesamt345    END346    EXEC QBM_PSQLCreate @viewname,347    'V',348      @gesamt,349      @UnComment = 0350  END TRY351  BEGIN CATCH352    EXEC QBM_PSessionErrorAdd DEFAULT353    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()354    RAISERROR(@Rethrow,355    18,356    1)357      WITH NOWAIT358  END CATCH359  endLabel:360  RETURN361END
Open raw exported source
SQL ยท Raw72 lines
1   create   procedure QBM_PViewBuild_FromAddOn (@uid_DialogTableTarget varchar(38)  ) as begin declare @QueryString nvarchar(max) declare @UID_QBMViewAddOn2 varchar(38) declare @Cmd nvarchar(max) declare @Gesamt nvarchar(max)  declare @SelectLine nvarchar(max) = '' declare @SelectSingleUnionPart nvarchar(max3) = '' declare @Maxlen int declare @ColumnName varchar(30) declare @Datatype nvarchar(64) declare @Ident_QBMViewAddOn nvarchar(64) declare @Viewname varchar4(30) declare @viewWhereClause nvarchar(max) declare @IsDeactivatedByPreProcessor bit declare @MyTempViewName nvarchar(100) declare @Columns table (UID_QBMViewAddOn5 varchar(38) collate database_default , columnName varchar(38) collate database_default , DATA_TYPE nvarchar(64) collate database_default , CHARACTER_MAXIMUM_LENGTH6 int ) declare @Datatypes table(Datatype nvarchar(64) collate database_default , SortOrder int ) declare @debuglevel nvarchar(1) = 'W' declare @DebugMessage7 nvarchar(max) declare @ErrorMessage nvarchar(4000) declare @ErrorSeverity int declare @ErrorState int declare @SynonymIntern nvarchar(16) = 'xxTab' declare8 @DebugSwitch int = 0 declare @ElementLast int declare @ElementBufferMulti QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare9 @ElementLast_c4 int declare @ElementBufferMulti_c4 QBM_YCursorBuffer declare @ElementCount_c4 int declare @ElementIndex_c4 int SET XACT_ABORT OFF BEGIN10 TRY insert into @Datatypes(Datatype, SortOrder) values ('nvarchar' , 0 ) , ('varchar' , 100 ) , ('nchar' , 200 ) , ('char' , 300 ) , ('varbinary' , 100011 ) , ('binary' , 1100 ) , ('float' , 2001 )  , ('datetime' , 2100 ) , ('numeric' , 3001 ) , ('bigint' , 3101 ) , ('int' , 3201 ) , ('smallint' , 3301 )12 , ('tinyint' , 3401 ) , ('bit' , 3501 ) select top 1 @Viewname = t.TableName , @viewWhereClause = dbo.QBM_FCVStringTrimRight( t.ViewWhereClause, ' '+CHAR13(9) + char(13) + char(10), 0) , @IsDeactivatedByPreProcessor = t.IsDeactivatedByPreProcessor from DialogTable t with (readpast) where t.UID_DialogTable14 = @uid_DialogTableTarget select @MyTempViewName = left(concat('TST_FTemporaryElement', reverse(dbo.QBM_FGICodeName('V', NEWID()) )), 30) if @IsDeactivatedByPreProcessor15 = 1 begin select @viewWhereClause = ' 1 = 0 ' end  if @DebugSwitch > 0 begin print '@Viewname ' + isnull(@Viewname, '<none>') print '@viewWhereClause '16 + isnull(@viewWhereClause, '<none>') end  delete  @ElementBufferMulti  insert into @ElementBufferMulti (UID1, ContentFull) select a.uid_QBMViewAddOn ,a.QueryString17  from QBMViewAddOn a left outer join QBMModuleDef m on a.UID_QBMViewAddOn like '___-%' and LEFT(a.UID_QBMViewAddOn, 3) = m.ModuleName where a.uid_dialogtable18 = @uid_DialogTableTarget order by ISNULL(m.SortOrder, POWER(2,30)) asc select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount19 +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @UID_QBMViewAddOn = bu.UID1 , @QueryString = bu.ContentFull20 from @ElementBufferMulti bu where bu.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print @UID_QBMViewAddOn print @QueryString end exec QBM_PViewDrop21 @MyTempViewName select @Cmd = 'create or alter view ' + @MyTempViewName + ' as ' + @QueryString exec sp_executesql @cmd insert into @Columns (UID_QBMViewAddOn22, columnName, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH) select @UID_QBMViewAddOn, left(c.COLUMN_NAME, 30), c.DATA_TYPE, case isnull(c.CHARACTER_MAXIMUM_LENGTH23, 0) when -1 then POWER(2,30) when 0 then 10 else c.CHARACTER_MAXIMUM_LENGTH end from INFORMATION_SCHEMA.VIEWS v  join INFORMATION_SCHEMA.COLUMNS c  on24 v.TABLE_NAME = c.TABLE_NAME where v.TABLE_NAME = @MyTempViewName select @ElementIndex += 1 end  exec QBM_PViewDrop @MyTempViewName   if not exists (select25 top 1 1 from @Columns ) begin select @debugmessage = 'no definition(s) found for View ' + @Viewname if @DebugSwitch > 0 print @debugmessage exec QBM_PJournal26 @debugmessage, @@procid, 'W', @debuglevel exec QBM_PViewdrop @Viewname goto endLabel end select @SelectLine = '' select @SelectSingleUnionPart = '' delete27  @ElementBufferMulti  insert into @ElementBufferMulti (Ident1, Int1) select c.columnName, MAX(CHARACTER_MAXIMUM_LENGTH) from @Columns c group by c.columnName28 order by c.columnName select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex29 <= @ElementLast begin select top 1 @columnname = bu.Ident1 , @maxlen = bu.Int1 from @ElementBufferMulti bu where bu.ElementIndex = @ElementIndex  select30 top 1 @Datatype = c.DATA_TYPE from @Columns c left outer join @Datatypes as w on c.DATA_TYPE = w.datatype where c.columnName = @ColumnName order by w.SortOrder31 asc select @SelectLine = @SelectLine + ', ' + @ColumnName  select @SelectSingleUnionPart = @SelectSingleUnionPart + ', convert(' + @Datatype + case when32 @Datatype like '%char%' or @Datatype like '%binary%' then case @Maxlen when POWER(2,30) then '(max)' else '(' + ltrim(STR(@maxlen)) + ')' end else '' 33end + ', null) as ' + @ColumnName select @ElementIndex += 1 end  if @DebugSwitch > 0 begin print '@SelectLine ' + isnull(@SelectLine, '<none>') print '@SelectSingleUnionPart '34 + isnull(@SelectSingleUnionPart, '<none>') end select @Gesamt = 'create or alter view dbo.' + @Viewname + ' as 35 select * from 36  (37	select ' + SUBSTRING38(@SelectSingleUnionPart, 2, LEN(@SelectSingleUnionPart) -1) + '39	 where 1=0 ' declare @ColumnEmpty varchar(30)  delete  @ElementBufferMulti  insert into40 @ElementBufferMulti (UID1, ContentFull, LongIdent1) select a.uid_QBMViewAddOn , a.QueryString, a.Ident_QBMViewAddOn from QBMViewAddOn a where a.uid_dialogtable41 = @uid_DialogTableTarget select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while42 @ElementIndex <= @ElementLast begin select top 1 @UID_QBMViewAddOn = bu.UID1 , @querystring = bu.ContentFull , @Ident_QBMViewAddOn = bu.LongIdent1 from43 @ElementBufferMulti bu where bu.ElementIndex = @ElementIndex select @SelectSingleUnionPart = ''   delete  @ElementBufferMulti_c4  insert into @ElementBufferMulti_c444 (Ident1, Ident2) select x.columnName , y.columnName from (select distinct c.columnName from @Columns c ) as x left outer join (select ct.columnName from45 @Columns ct where ct.UID_QBMViewAddOn = @UID_QBMViewAddOn ) as y on x.columnName = y.columnName order by x.columnName select @ElementCount_c4 = @@ROWCOUNT46 select @ElementIndex_c4 = @@IDENTITY - @ElementCount_c4 +1 select @ElementLast_c4 = @@IDENTITY while @ElementIndex_c4 <= @ElementLast_c4 begin select 47top 1 @columnname = bu.Ident1 , @columnempty = bu.Ident2 from @ElementBufferMulti_c4 bu where bu.ElementIndex = @ElementIndex_c4 select @SelectSingleUnionPart48 = @SelectSingleUnionPart + ', ' + case  when @columnempty IS null and exists (select top 1 1 from @Columns c join @Datatypes t on c.DATA_TYPE = t.Datatype49 where c.columnName = @ColumnName and t.SortOrder % 2 = 1 )  then ' convert(bit, 0) as '  when @columnempty IS null then ' null as ' else @SynonymIntern50 + '.' end + @ColumnName select @ElementIndex_c4 += 1 end  select @Gesamt = @Gesamt + '51	union all52	-- part from ' + isnull(@Ident_QBMViewAddOn, @UID_QBMViewAddOn53) + '54	select ' + SUBSTRING(@SelectSingleUnionPart, 2, len(@SelectSingleUnionPart) -1) + '55	 from (56' + dbo.QBM_FCVStringToIndent(@querystring, 3) +57'58		) as ' + @SynonymIntern if @DebugSwitch > 0 begin print '------------' select @DebugMessage = 'select ' + SUBSTRING(@SelectSingleUnionPart, 2, len59(@SelectSingleUnionPart) -1) + '60			 from (61		' + dbo.QBM_FCVStringToIndent(@querystring, 3) +'62				) as ' + @SynonymIntern print @DebugMessage end 63select @ElementIndex += 1 end   select @Gesamt = @Gesamt + '64 ) as x' if @viewWhereClause > ' '  and not exists (select top 1 1 from QBMViewAddOn a where65 a.UID_DialogTable = @uid_DialogTableTarget and a.QueryString = @viewWhereClause ) begin select @Gesamt = @Gesamt + '66 where (67 ' + dbo.QBM_FCVStringToIndent68( @viewWhereClause, 2) + '69		)' end if @DebugSwitch > 0 begin select @gesamt end exec QBM_PSQLCreate @viewname, 'V', @gesamt, @UnComment = 0 END TRY BEGIN70 CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END 71CATCH endLabel: return end 72