dbo.QBM_PViewBuild_FromAddOn

1   create   procedure QBM_PViewBuild_FromAddOn (@uid_DialogTableTarget varchar(38)  ) as begin declare @QueryString nvarchar(max) declare @UID_QBMViewAddOn
2 varchar(38) declare @Cmd nvarchar(max) declare @Gesamt nvarchar(max)  declare @SelectLine nvarchar(max) = '' declare @SelectSingleUnionPart nvarchar(max
3) = '' declare @Maxlen int declare @ColumnName varchar(30) declare @Datatype nvarchar(64) declare @Ident_QBMViewAddOn nvarchar(64) declare @Viewname varchar
4(30) declare @viewWhereClause nvarchar(max) declare @IsDeactivatedByPreProcessor bit declare @MyTempViewName nvarchar(100) declare @Columns table (UID_QBMViewAddOn
5 varchar(38) collate database_default , columnName varchar(38) collate database_default , DATA_TYPE nvarchar(64) collate database_default , CHARACTER_MAXIMUM_LENGTH
6 int ) declare @Datatypes table(Datatype nvarchar(64) collate database_default , SortOrder int ) declare @debuglevel nvarchar(1) = 'W' declare @DebugMessage
7 nvarchar(max) declare @ErrorMessage nvarchar(4000) declare @ErrorSeverity int declare @ErrorState int declare @SynonymIntern nvarchar(16) = 'xxTab' declare
8 @DebugSwitch int = 0 declare @ElementLast int declare @ElementBufferMulti QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare
9 @ElementLast_c4 int declare @ElementBufferMulti_c4 QBM_YCursorBuffer declare @ElementCount_c4 int declare @ElementIndex_c4 int SET XACT_ABORT OFF BEGIN
10 TRY insert into @Datatypes(Datatype, SortOrder) values ('nvarchar' , 0 ) , ('varchar' , 100 ) , ('nchar' , 200 ) , ('char' , 300 ) , ('varbinary' , 1000
11 ) , ('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, ' '+CHAR
13(9) + char(13) + char(10), 0) , @IsDeactivatedByPreProcessor = t.IsDeactivatedByPreProcessor from DialogTable t with (readpast) where t.UID_DialogTable
14 = @uid_DialogTableTarget select @MyTempViewName = left(concat('TST_FTemporaryElement', reverse(dbo.QBM_FGICodeName('V', NEWID()) )), 30) if @IsDeactivatedByPreProcessor
15 = 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.QueryString
17  from QBMViewAddOn a left outer join QBMModuleDef m on a.UID_QBMViewAddOn like '___-%' and LEFT(a.UID_QBMViewAddOn, 3) = m.ModuleName where a.uid_dialogtable
18 = @uid_DialogTableTarget order by ISNULL(m.SortOrder, POWER(2,30)) asc select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount
19 +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @UID_QBMViewAddOn = bu.UID1 , @QueryString = bu.ContentFull
20 from @ElementBufferMulti bu where bu.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print @UID_QBMViewAddOn print @QueryString end exec QBM_PViewDrop
21 @MyTempViewName select @Cmd = 'create or alter view ' + @MyTempViewName + ' as ' + @QueryString exec sp_executesql @cmd insert into @Columns (UID_QBMViewAddOn
22, columnName, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH) select @UID_QBMViewAddOn, left(c.COLUMN_NAME, 30), c.DATA_TYPE, case isnull(c.CHARACTER_MAXIMUM_LENGTH
23, 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  on
24 v.TABLE_NAME = c.TABLE_NAME where v.TABLE_NAME = @MyTempViewName select @ElementIndex += 1 end  exec QBM_PViewDrop @MyTempViewName   if not exists (select
25 top 1 1 from @Columns ) begin select @debugmessage = 'no definition(s) found for View ' + @Viewname if @DebugSwitch > 0 print @debugmessage exec QBM_PJournal
26 @debugmessage, @@procid, 'W', @debuglevel exec QBM_PViewdrop @Viewname goto endLabel end select @SelectLine = '' select @SelectSingleUnionPart = '' delete
27  @ElementBufferMulti  insert into @ElementBufferMulti (Ident1, Int1) select c.columnName, MAX(CHARACTER_MAXIMUM_LENGTH) from @Columns c group by c.columnName
28 order by c.columnName select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex
29 <= @ElementLast begin select top 1 @columnname = bu.Ident1 , @maxlen = bu.Int1 from @ElementBufferMulti bu where bu.ElementIndex = @ElementIndex  select
30 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.SortOrder
31 asc select @SelectLine = @SelectLine + ', ' + @ColumnName  select @SelectSingleUnionPart = @SelectSingleUnionPart + ', convert(' + @Datatype + case when
32 @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 ' + SUBSTRING
38(@SelectSingleUnionPart, 2, LEN(@SelectSingleUnionPart) -1) + '
39	 where 1=0 ' declare @ColumnEmpty varchar(30)  delete  @ElementBufferMulti  insert into
40 @ElementBufferMulti (UID1, ContentFull, LongIdent1) select a.uid_QBMViewAddOn , a.QueryString, a.Ident_QBMViewAddOn from QBMViewAddOn a where a.uid_dialogtable
41 = @uid_DialogTableTarget select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while
42 @ElementIndex <= @ElementLast begin select top 1 @UID_QBMViewAddOn = bu.UID1 , @querystring = bu.ContentFull , @Ident_QBMViewAddOn = bu.LongIdent1 from
43 @ElementBufferMulti bu where bu.ElementIndex = @ElementIndex select @SelectSingleUnionPart = ''   delete  @ElementBufferMulti_c4  insert into @ElementBufferMulti_c4
44 (Ident1, Ident2) select x.columnName , y.columnName from (select distinct c.columnName from @Columns c ) as x left outer join (select ct.columnName from
45 @Columns ct where ct.UID_QBMViewAddOn = @UID_QBMViewAddOn ) as y on x.columnName = y.columnName order by x.columnName select @ElementCount_c4 = @@ROWCOUNT
46 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 @SelectSingleUnionPart
48 = @SelectSingleUnionPart + ', ' + case  when @columnempty IS null and exists (select top 1 1 from @Columns c join @Datatypes t on c.DATA_TYPE = t.Datatype
49 where c.columnName = @ColumnName and t.SortOrder % 2 = 1 )  then ' convert(bit, 0) as '  when @columnempty IS null then ' null as ' else @SynonymIntern
50 + '.' end + @ColumnName select @ElementIndex_c4 += 1 end  select @Gesamt = @Gesamt + '
51	union all
52	-- part from ' + isnull(@Ident_QBMViewAddOn, @UID_QBMViewAddOn
53) + '
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, len
59(@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 where
65 a.UID_DialogTable = @uid_DialogTableTarget and a.QueryString = @viewWhereClause ) begin select @Gesamt = @Gesamt + '
66 where (
67 ' + dbo.QBM_FCVStringToIndent
68( @viewWhereClause, 2) + '
69		)' end if @DebugSwitch > 0 begin select @gesamt end exec QBM_PSQLCreate @viewname, 'V', @gesamt, @UnComment = 0 END TRY BEGIN
70 CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END 
71CATCH endLabel: return end 
72