Back to OIM Explorer

dbo.QBM_FCVFKDollarToRelationMVP

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 1.819 characters

Interpretation

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

Relations

  • No extracted relations.

Typed Edges

  • No typed edges extracted for this source.

References

  • No direct source references extracted.

Referenced By

Complete Source

SQL85 lines
1CREATE FUNCTION dbo.QBM_FCVFKDollarToRelationMVP(2  @StartTable varchar(30),3  @DollarNotation varchar(1024)4) RETURNS varchar(10245)6AS7BEGIN8  DECLARE @erg varchar(1024)9  DECLARE @relations TABLE(SortOrder int identity,10  Childtable varchar(30) collate database_default,11  ChildColumn varchar(30) collate database_default,12  ParentTable varchar(30) collate database_default,13  ParentColumn varchar(30) collate database_default,14  UID_QBMRelation varchar(38) collate database_default,15  Portion varchar(1024) collate database_default)16  INSERT INTO @relations(ChildColumn,17  Portion)18  SELECT19    replace(replace(s.value, 'FK(', ''),20    ')',21    ''),22    s.value23  FROM string_split(@DollarNotation,24  '.') s25  IF EXISTS(26    SELECT TOP 1 127    FROM @relations r28    WHERE29      isnull(r.ChildColumn, '') = '')30  BEGIN31    GOTO Auswertung32  END33  UPDATE @relations34  SET Childtable = @starttable35  WHERE36    SortOrder = 137  DECLARE @lauf int = 138  WHILE @lauf > 039  BEGIN40    UPDATE @relations41    SET ParentTable = r.ParentTable,42    ParentColumn = r.ParentColumn,43    UID_QBMRelation = r.UID_QBMRelation44    FROM @relations w45    JOIN QBM_VQBMRelation r46      ON w.ChildColumn = r.ChildColumn AND w.Childtable = r.ChildTable47    JOIN DialogTable t48      WITH(readpast)49      ON r.UID_DialogTableParent = t.UID_DialogTable AND t.TableType IN('T',50    'V')51    WHERE52      w.ParentTable IS NULL53    SELECT @lauf = @@ROWCOUNT54    UPDATE @relations55    SET Childtable = x.ParentTable56    FROM @relations w57    JOIN(58    SELECT *59    FROM @relations) x60      ON w.SortOrder = x.SortOrder +161    WHERE62      w.Childtable IS NULL AND x.ParentTable > ' '63    SELECT @lauf += @@ROWCOUNT64  END65  Auswertung:66  IF EXISTS(67    SELECT TOP 1 168    FROM @relations r69    WHERE70      r.UID_QBMRelation IS NULL)71  BEGIN72    SELECT @erg = @DollarNotation73  END74  ELSE75  BEGIN76    SELECT77      @erg = STRING_AGG(r.UID_QBMRelation,78      char(7)) within79      GROUP(80    ORDER BY(r.Sortorder))81    FROM @relations r82  END83  endLabel:84  RETURN(@erg)85END
Open raw exported source
SQL ยท Raw13 lines
1      create   function dbo.QBM_FCVFKDollarToRelationMVP (@StartTable varchar(30) , @DollarNotation varchar(1024) ) returns varchar(1024) as begin2 declare @erg varchar(1024) declare @relations table (SortOrder int identity , Childtable varchar(30) collate database_default , ChildColumn varchar(303) collate database_default , ParentTable varchar(30) collate database_default , ParentColumn varchar(30) collate database_default , UID_QBMRelation varchar4(38) collate database_default , Portion varchar(1024) collate database_default ) insert into @relations(ChildColumn, Portion) select replace(replace(s.value5 , 'FK(', '') , ')', '') , s.value from string_split(@DollarNotation, '.') s if exists (select top 1 1 from @relations r where isnull(r.ChildColumn, ''6) = '' ) begin goto Auswertung end update @relations set Childtable = @starttable where SortOrder = 1 declare @lauf int = 1 while @lauf > 0 begin update7 @relations set ParentTable = r.ParentTable , ParentColumn = r.ParentColumn , UID_QBMRelation = r.UID_QBMRelation from @relations w join QBM_VQBMRelation8 r on w.ChildColumn = r.ChildColumn and w.Childtable = r.ChildTable join DialogTable t with (readpast) on r.UID_DialogTableParent = t.UID_DialogTable and9 t.TableType in ('T', 'V') where w.ParentTable is null select @lauf = @@ROWCOUNT update @relations set Childtable = x.ParentTable from @relations w join10 (select * from @relations ) x on w.SortOrder = x.SortOrder +1 where w.Childtable is null and x.ParentTable > ' ' select @lauf += @@ROWCOUNT end  Auswertung:11 if exists (select top 1 1 from @relations r where r.UID_QBMRelation is null ) begin select @erg = @DollarNotation end else begin select @erg = STRING_AGG12(r.UID_QBMRelation, char(7)) within group(order by (r.Sortorder)) from @relations r end endLabel: return(@erg) end 13