Back to OIM Explorer

dbo.QBM_FSQCEFTableDisplay

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 4.699 characters

Interpretation

Database function. Usually supports views, validation, or calculated predicates; look at referenced-by entries for callers.

Relations

No extracted relations.

Typed Edges

references source dbo.QBM_FCVStringToList source text reference
references source dbo.QBM_FCVIntToString source text reference
references source dbo.QBM_FCVStringToDigits source text reference
references source dbo.QBM_FCVStringToIndent source text reference
references source dbo.QBM_FCVStringToInt source text reference
references source dbo.QBM_FGI093F4EF597272C7DCA06D_1 source text reference

References

Referenced By

dbo.QBM_FSQCEFParmSelect

Complete Source

1CREATE FUNCTION dbo.QBM_FSQCEFTableDisplay(2  @TableName nvarchar(64),3  @Alias nvarchar(16)4) RETURNS nvarchar(max5)6AS7BEGIN8  DECLARE @DisplayPattern nvarchar(max) = ''9  DECLARE @UID_DialogTable varchar(38)10  DECLARE @Work nvarchar(max) = ''11  DECLARE @ColumnName nvarchar(64)12  DECLARE @SchemaDatatype nvarchar(64)13  DECLARE @ParentTable varchar(30)14  DECLARE @ParentColumn varchar(30)15  DECLARE @erg nvarchar(max)16  DECLARE @ElementBuffer QBM_YCursorBuffer17  DECLARE @ElementCount int18  DECLARE @ElementIndex int19  DECLARE @RekLevel int = 020  DECLARE @MaxRekLevel int = 321  SELECT22    @RekLevel = dbo.QBM_FCVStringToInt(dbo.QBM_FCVStringToDigits(@Alias),23    0)24  DECLARE @subalias varchar(64) = 'p' + dbo.QBM_FCVIntToString(@RekLevel + 1)25  DECLARE @Erst_OrderNumber int = 026  DECLARE @Erst_OrderNumber_Old int = 027  DECLARE @Zweit_OrderNumberReverse int = 028  DECLARE @Zweit_OrderNumberReverse_old int = 029  DECLARE @ZweitOrderNumber int = 030  DECLARE @CountDisplayItems int31  DECLARE @Literal nvarchar(400)32  IF @RekLevel > @MaxRekLevel33  BEGIN34    GOTO Endlabel35  END36  SELECT37    TOP 1 @DisplayPattern = t.DisplayPattern,38    @UID_DialogTable = t.UID_DialogTable39  FROM DialogTable t40  WHERE41    t.TableName = @Tablename42  IF isnull(@DisplayPattern,43  N '') = N ''44  BEGIN45    SELECT46      TOP 1 @DisplayPattern = string_agg(CONCAT('%', x.ColumnName, '%'),47      ' - ') within48      GROUP(49    ORDER BY x.columnname)50    FROM(51    SELECT c.ColumnName52    FROM dialogtable t53      WITH(readpast)54    JOIN DialogColumn c55      WITH(readpast)56      ON t.UID_DialogTable = c.UID_DialogTable AND t.TableName = @TableName AND c.IsPKMember = 1) AS x57    IF isnull(@DisplayPattern,58    N '') = N ''59    BEGIN60      GOTO Endlabel61    END62  END63  INSERT INTO @ElementBuffer(Int1,64  Int2,65  Ident1,66  Ident2,67  Ident3,68  LongIdent1,69  Int3,70  LongIdent2)71  SELECT72    erst.OrderNumber,73    zweit.OrderNumberReverse,74    c.ColumnName,75    c.SchemaDataType,76    r.ParentTable,77    r.ParentColumn,78    zweit.OrderNumber,79    zweit.ParameterValue80  FROM(81  SELECT82    s1.OrderNumber,83    s1.ParameterValue84  FROM dbo.QBM_FCVStringToList(@displaypattern, '%', 1, 0) s1) AS erst85  OUTER apply dbo.QBM_FCVStringToList(erst.parameterValue,86  '?',87  1,88  0) AS zweit89  LEFT90  OUTER91  JOIN DialogColumn c92    ON c.UID_DialogTable = @uid_dialogTable AND c.ColumnName = zweit.ParameterValue93  LEFT94  OUTER95  JOIN QBM_VQBMRelation r96    ON r.ChildTable = @TableName AND r.ChildColumn = c.ColumnName AND c.SchemaDataLen = 3897  ORDER BY erst.OrderNumber,98  zweit.OrderNumber99  SELECT @ElementCount = @@ROWCOUNT100  SELECT @ElementIndex = 1101  SELECT102    @erg = 'concat( '''', ''''  '103  SELECT TOP 1 @CountDisplayItems = max(bu.Int1)104  FROM @ElementBuffer bu105  WHILE @ElementIndex <= @ElementCount106  BEGIN107    SELECT @Erst_OrderNumber_Old = @Erst_OrderNumber108    SELECT @Zweit_OrderNumberReverse_old = @Zweit_OrderNumberReverse109    SELECT110      TOP 1 @Erst_OrderNumber = bu.Int1,111      @Zweit_OrderNumberReverse = bu.int2,112      @Columnname = bu.Ident1,113      @SchemaDatatype = bu.Ident2,114      @ParentTable = bu.Ident3,115      @ParentColumn = bu.LongIdent1,116      @ZweitOrderNumber = bu.Int3,117      @Literal = bu.LongIdent2118    FROM @ElementBuffer bu119    WHERE120      bu.ElementIndex = @ElementIndex121    IF @ElementIndex > 1 AND @Erst_OrderNumber <> @Erst_OrderNumber_Old122    BEGIN123      SELECT124        @erg = CONCAT(@erg,125        char(13),126        char(10),127        N ' , N'' '' ')128    END129    IF @ParentTable > ' '130    BEGIN131      IF @RekLevel >= @MaxRekLevel132      BEGIN133        SELECT134          @erg = CONCAT(@erg,135          char(13),136          char(10),137          '  , ''''  ')138        SELECT @Work = ''139      END140      ELSE141      BEGIN142        SELECT143          @Work = ' (select top 1 144			' + dbo.QBM_FSQCEFTableDisplay(@ParentTable,145          @subalias) + '146			from ' + @ParentTable + ' ' + @subalias + ' with (nolock)147			where ' + @subalias + '.' + @ParentColumn + '  = ' + @Alias + '.'148          + @Columnname + ' 149		)150	'151      END152    END153    ELSE154    BEGIN155      IF @ColumnName IS NULL156      BEGIN157        SELECT158          @work = CONCAT('''',159          replace(@Literal, '''', ''''''),160          '''')161      END162      ELSE163      BEGIN164        SELECT165          @work = dbo.QBM_FGI093F4EF597272C7DCA06D_1(@Erst_OrderNumber,166          @Zweit_OrderNumberReverse,167          @columnname,168          @Alias,169          @SchemaDatatype,170          @ParentTable,171          @CountDisplayItems)172      END173    END174    IF @Erst_OrderNumber <> @Erst_OrderNumber_Old AND @Work > ' '175    BEGIN176      SELECT177        @erg = CONCAT(@erg,178        char(13),179        char(10),180        ', ',181        @work)182    END183    ELSE184    BEGIN185      IF @Zweit_OrderNumberReverse <> @Zweit_OrderNumberReverse_old186      BEGIN187        SELECT188          @erg = replace(@erg,189          CONCAT('#', trim(str(@Erst_OrderNumber)), trim(str(@Zweit_OrderNumberReverse)), '#'),190          dbo.QBM_FCVStringToIndent(@Work, @ZweitOrderNumber * 3))191        SELECT @Work = ''192      END193      ELSE194      BEGIN195        SELECT196          @erg = replace(@erg,197          CONCAT('#', trim(str(@Erst_OrderNumber)), trim(str(@Zweit_OrderNumberReverse)), '#'),198          CONCAT('/* irregular Display, no parent */ ''', trim(str(@Erst_OrderNumber)), trim(str(@Zweit_OrderNumberReverse)),199          ''''))200        SELECT @Work = ''201      END202    END203    SELECT @ElementIndex += 1204  END205  SELECT206    @erg = N 'left(' + @erg + N '), 255)' endLabel:207  RETURN(@erg)208END

Open raw exported source

1  create   function dbo.QBM_FSQCEFTableDisplay(@TableName nvarchar(64), @Alias nvarchar(16)  ) returns nvarchar(max) as begin declare @DisplayPattern2 nvarchar(max) = '' declare @UID_DialogTable varchar(38) declare @Work nvarchar(max) = '' declare @ColumnName nvarchar(64) declare @SchemaDatatype nvarchar3(64) declare @ParentTable varchar(30) declare @ParentColumn varchar(30) declare @erg nvarchar(max) declare @ElementBuffer QBM_YCursorBuffer        declare4 @ElementCount int declare @ElementIndex int declare @RekLevel int = 0 declare @MaxRekLevel int = 3 select @RekLevel = dbo.QBM_FCVStringToInt(dbo.QBM_FCVStringToDigits5(@Alias ) , 0) declare @subalias varchar(64) = 'p' + dbo.QBM_FCVIntToString( @RekLevel + 1) declare @Erst_OrderNumber int = 0 declare @Erst_OrderNumber_Old6 int = 0 declare @Zweit_OrderNumberReverse int = 0 declare @Zweit_OrderNumberReverse_old int = 0 declare @ZweitOrderNumber int = 0 declare @CountDisplayItems7 int declare @Literal nvarchar(400)  if @RekLevel > @MaxRekLevel begin goto Endlabel end select top 1 @DisplayPattern = t.DisplayPattern , @UID_DialogTable8 = t.UID_DialogTable from DialogTable t where t.TableName = @Tablename if isnull(@DisplayPattern, N'') = N'' begin  select top 1 @DisplayPattern = string_agg9( concat( '%' , x.ColumnName ,'%' )  , ' - ' )  within group (order by x.columnname)   from ( select c.ColumnName from dialogtable t with (readpast) join10 DialogColumn c with (readpast) on t.UID_DialogTable = c.UID_DialogTable and t.TableName = @TableName and c.IsPKMember = 1  ) as x if isnull(@DisplayPattern11, N'') = N'' begin goto Endlabel end end insert into @ElementBuffer(Int1, Int2, Ident1, Ident2, Ident3, LongIdent1, Int3  , LongIdent2 ) select erst.OrderNumber12,   zweit.OrderNumberReverse, c.ColumnName, c.SchemaDataType, r.ParentTable , r.ParentColumn, zweit.OrderNumber , zweit.ParameterValue from ( select s1.OrderNumber13, 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_VQBMRelation15 r on r.ChildTable = @TableName and r.ChildColumn = c.ColumnName and c.SchemaDataLen = 38 order by erst.OrderNumber, zweit.OrderNumber select @ElementCount16 = @@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_OrderNumberReverse18 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 = @ElementIndex20 if @ElementIndex > 1 and @Erst_OrderNumber <> @Erst_OrderNumber_Old begin  select @erg = concat(@erg , char(13), char(10) , N' , N'' '' ' ) end if @ParentTable21 > ' ' begin  if @RekLevel >= @MaxRekLevel begin select @erg = concat(@erg , char(13), char(10) , '  , ''''  ' ) select @Work = '' end else begin  select22 @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_OrderNumberReverse30, @columnname , @Alias, @SchemaDatatype, @ParentTable, @CountDisplayItems) end     end if @Erst_OrderNumber <> @Erst_OrderNumber_Old and @Work > ' ' begin31 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_OrderNumberReverse34)), '#') , 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