dbo.QBM_FSQCEFTableDisplay

1  create   function dbo.QBM_FSQCEFTableDisplay(@TableName nvarchar(64), @Alias nvarchar(16)  ) returns nvarchar(max) as begin declare @DisplayPattern
2 nvarchar(max) = '' declare @UID_DialogTable varchar(38) declare @Work nvarchar(max) = '' declare @ColumnName nvarchar(64) declare @SchemaDatatype nvarchar
3(64) declare @ParentTable varchar(30) declare @ParentColumn varchar(30) declare @erg nvarchar(max) declare @ElementBuffer QBM_YCursorBuffer        declare
4 @ElementCount int declare @ElementIndex int declare @RekLevel int = 0 declare @MaxRekLevel int = 3 select @RekLevel = dbo.QBM_FCVStringToInt(dbo.QBM_FCVStringToDigits
5(@Alias ) , 0) declare @subalias varchar(64) = 'p' + dbo.QBM_FCVIntToString( @RekLevel + 1) declare @Erst_OrderNumber int = 0 declare @Erst_OrderNumber_Old
6 int = 0 declare @Zweit_OrderNumberReverse int = 0 declare @Zweit_OrderNumberReverse_old int = 0 declare @ZweitOrderNumber int = 0 declare @CountDisplayItems
7 int declare @Literal nvarchar(400)  if @RekLevel > @MaxRekLevel begin goto Endlabel end select top 1 @DisplayPattern = t.DisplayPattern , @UID_DialogTable
8 = t.UID_DialogTable from DialogTable t where t.TableName = @Tablename if isnull(@DisplayPattern, N'') = N'' begin  select top 1 @DisplayPattern = string_agg
9( concat( '%' , x.ColumnName ,'%' )  , ' - ' )  within group (order by x.columnname)   from ( select c.ColumnName from dialogtable t with (readpast) join
10 DialogColumn c with (readpast) on t.UID_DialogTable = c.UID_DialogTable and t.TableName = @TableName and c.IsPKMember = 1  ) as x if isnull(@DisplayPattern
11, N'') = N'' begin goto Endlabel end end insert into @ElementBuffer(Int1, Int2, Ident1, Ident2, Ident3, LongIdent1, Int3  , LongIdent2 ) select erst.OrderNumber
12,   zweit.OrderNumberReverse, c.ColumnName, c.SchemaDataType, r.ParentTable , r.ParentColumn, zweit.OrderNumber , zweit.ParameterValue from ( select s1.OrderNumber
13, s1.ParameterValue from dbo.QBM_FCVStringToList(@displaypattern, '%', 1,0) s1 ) as erst outer apply dbo.QBM_FCVStringToList(erst.parameterValue, '?', 
141,0) as zweit left outer join DialogColumn c on c.UID_DialogTable = @uid_dialogTable and c.ColumnName = zweit.ParameterValue left outer join QBM_VQBMRelation
15 r on r.ChildTable = @TableName and r.ChildColumn = c.ColumnName and c.SchemaDataLen = 38 order by erst.OrderNumber, zweit.OrderNumber select @ElementCount
16 = @@ROWCOUNT select @ElementIndex = 1     select @erg = 'concat( '''', ''''  ' select top 1 @CountDisplayItems = max(bu.Int1)  from @ElementBuffer bu 
17while @ElementIndex <= @ElementCount begin select @Erst_OrderNumber_Old = @Erst_OrderNumber select @Zweit_OrderNumberReverse_old = @Zweit_OrderNumberReverse
18 select top 1 @Erst_OrderNumber = bu.Int1 , @Zweit_OrderNumberReverse = bu.int2 , @Columnname = bu.Ident1 , @SchemaDatatype = bu.Ident2 , @ParentTable 
19= bu.Ident3  , @ParentColumn = bu.LongIdent1 , @ZweitOrderNumber = bu.Int3 , @Literal = bu.LongIdent2 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex
20 if @ElementIndex > 1 and @Erst_OrderNumber <> @Erst_OrderNumber_Old begin  select @erg = concat(@erg , char(13), char(10) , N' , N'' '' ' ) end if @ParentTable
21 > ' ' begin  if @RekLevel >= @MaxRekLevel begin select @erg = concat(@erg , char(13), char(10) , '  , ''''  ' ) select @Work = '' end else begin  select
22 @Work = ' (select top 1 
23			' + dbo.QBM_FSQCEFTableDisplay(@ParentTable, @subalias) + '
24			from ' + @ParentTable + ' ' + @subalias + ' with (nolock)
25			where '
26 + @subalias + '.' + @ParentColumn + '  = ' + @Alias + '.' + @Columnname + ' 
27		)
28	' end  end else begin if @ColumnName is null begin select @work = 
29concat('''', replace(@Literal , '''', '''''' ), '''') end else begin select @work = dbo.QBM_FGI093F4EF597272C7DCA06D_1(@Erst_OrderNumber, @Zweit_OrderNumberReverse
30, @columnname , @Alias, @SchemaDatatype, @ParentTable, @CountDisplayItems) end     end if @Erst_OrderNumber <> @Erst_OrderNumber_Old and @Work > ' ' begin
31 select @erg = concat(@erg , char(13), char(10) , ', ' , @work) end else begin    if @Zweit_OrderNumberReverse <> @Zweit_OrderNumberReverse_old  begin 
32select @erg = replace(@erg , concat('#', trim(str(@Erst_OrderNumber)) , trim(str(@Zweit_OrderNumberReverse)), '#') , dbo.QBM_FCVStringToIndent( @Work, 
33@ZweitOrderNumber * 3 ) ) select @Work = '' end else begin  select @erg = replace(@erg , concat('#', trim(str(@Erst_OrderNumber)) , trim(str(@Zweit_OrderNumberReverse
34)), '#') , concat('/* irregular Display, no parent */ ''', trim(str(@Erst_OrderNumber)) , trim(str(@Zweit_OrderNumberReverse)), '''') ) select @Work = 
35'' end end select @ElementIndex += 1 end  select @erg = N'left(' + @erg + N'), 255)' endLabel: return(@erg) end 
36