Back to OIM Explorer

dbo.QBM_FCVListToStringeMergeSort

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 1.388 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_FCVStringToListSplitted source text reference
  • references source dbo.QBM_FCVListToStringeMerge source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL71 lines
1CREATE FUNCTION dbo.QBM_FCVListToStringeMergeSort(2  @dest nvarchar(max),3  @original nvarchar(max)4) RETURNS nvarchar(max5)6AS7BEGIN8  DECLARE @erg nvarchar(max)9  DECLARE @destTable QBM_YParameterList10  DECLARE @OrigTable QBM_YParameterList11  INSERT INTO @destTable(ContentShort,12  ContentFull)13  SELECT14    b.ParameterName,15    max(b.ParameterValue)16  FROM dbo.QBM_FCVStringToListSplitted(@dest,17  NCHAR(7),18  1,19  0,20  N '=') b21  GROUP BY b.ParameterName22  ORDER BY b.ParameterName23  INSERT INTO @OrigTable(ContentShort,24  ContentFull)25  SELECT26    a.ParameterName,27    max(a.ParameterValue)28  FROM dbo.QBM_FCVStringToListSplitted(@original,29  NCHAR(7),30  1,31  0,32  N '=') a33  GROUP BY a.ParameterName34  ORDER BY a.ParameterName35  INSERT INTO @destTable(ContentShort,36  ContentFull)37  SELECT38    a.ContentShort,39    a.ContentFull40  FROM @OrigTable a41  WHERE42    NOT EXISTS(43  SELECT TOP 1 144  FROM @destTable b45  WHERE46    b.ContentShort = a.ContentShort)47  UPDATE @destTable48  SET ContentFull = b.ContentFull49  FROM @destTable a50  JOIN @OrigTable b51    ON a.ContentShort = b.ContentShort52  WHERE53    isnull(a.ContentFull,54  '') <> isnull(b.ContentFull,55  '')56  SELECT57    @erg = string_agg(convert(nvarchar(max), x.wert),58    nchar(7)) within59    GROUP(60  ORDER BY x.ContentShort)61  FROM(62  SELECT63    d.ContentShort,64    CONCAT(d.ContentShort, CASE65    WHEN contentfull > ' ' THEN66    CONCAT(N '=', d.ContentFull)67  ELSE ''68  END) AS Wert69  FROM @destTable d) AS x ende:70  RETURN(@erg)71END
Open raw exported source
SQL ยท Raw10 lines
1    create   function dbo.QBM_FCVListToStringeMergeSort (@dest nvarchar(max) , @original nvarchar(max) ) returns nvarchar(max) as begin declare 2@erg nvarchar(max) declare @destTable QBM_YParameterList  declare @OrigTable QBM_YParameterList  insert into @destTable( ContentShort , ContentFull ) select3 b.ParameterName, max(b.ParameterValue) from dbo.QBM_FCVStringToListSplitted(@dest , NCHAR(7), 1,0, N'=') b group by b.ParameterName order by b.ParameterName4 insert into @OrigTable( ContentShort , ContentFull ) select a.ParameterName, max(a.ParameterValue) from dbo.QBM_FCVStringToListSplitted(@original, NCHAR5(7), 1,0, N'=') a group by a.ParameterName order by a.ParameterName insert into @destTable(ContentShort , ContentFull ) select a.ContentShort , a.ContentFull6  from @OrigTable a where not exists (select top 1 1 from @destTable b where b.ContentShort  = a.ContentShort  ) update @destTable set ContentFull  = b.ContentFull7  from @destTable a join @OrigTable b on a.ContentShort  = b.ContentShort  where isnull(a.ContentFull , '') <> isnull(b.ContentFull , '') select @erg =8 string_agg(convert(nvarchar(max), x.wert )  , nchar(7) )  within group (order by x.ContentShort  ) from ( select d.ContentShort  ,concat(d.ContentShort9  , case when contentfull > ' ' then concat(N'=' , d.ContentFull) else '' end ) as Wert from @destTable d ) as x ende: return (@erg) end 10