Back to OIM Explorer

dbo.QBM_FCVListToStringeMerge

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 1.457 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_FCVIntToString source text reference

References

Referenced By

Complete Source

SQL73 lines
1CREATE FUNCTION dbo.QBM_FCVListToStringeMerge(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(Parameter1,12  ContentShort,13  ContentFull)14  SELECT15    dbo.QBM_FCVIntToString(max(b.OrderNumber) * 1000),16    b.ParameterName,17    max(b.ParameterValue)18  FROM dbo.QBM_FCVStringToListSplitted(@dest,19  NCHAR(7),20  1,21  0,22  N '=') b23  GROUP BY b.ParameterName24  INSERT INTO @OrigTable(Parameter1,25  ContentShort,26  ContentFull)27  SELECT28    dbo.QBM_FCVIntToString(max(a.OrderNumber) * 1000 -998),29    a.ParameterName,30    max(a.ParameterValue)31  FROM dbo.QBM_FCVStringToListSplitted(@original,32  NCHAR(7),33  1,34  0,35  N '=') a36  GROUP BY a.ParameterName37  INSERT INTO @destTable(Parameter1,38  ContentShort,39  ContentFull)40  SELECT41    a.Parameter1,42    a.ContentShort,43    a.ContentFull44  FROM @OrigTable a45  WHERE46    NOT EXISTS(47  SELECT TOP 1 148  FROM @destTable b49  WHERE50    b.ContentShort = a.ContentShort)51  UPDATE @destTable52  SET ContentFull = b.ContentFull53  FROM @destTable a54  JOIN @OrigTable b55    ON a.ContentShort = b.ContentShort56  WHERE57    isnull(a.ContentFull,58  '') <> isnull(b.ContentFull,59  '')60  SELECT61    @erg = string_agg(convert(nvarchar(max), x.wert),62    nchar(7))63  FROM(64  SELECT65    d.ContentShort,66    CONCAT(d.ContentShort, CASE67    WHEN contentfull > ' ' THEN68    CONCAT(N '=', d.ContentFull)69  ELSE ''70  END) AS Wert71  FROM @destTable d) AS x ende:72  RETURN(@erg)73END
Open raw exported source
SQL ยท Raw11 lines
1   create   function dbo.QBM_FCVListToStringeMerge (@dest nvarchar(max) , @original nvarchar(max) ) returns nvarchar(max) as begin declare @erg 2nvarchar(max)  declare @destTable QBM_YParameterList  declare @OrigTable QBM_YParameterList  insert into @destTable(Parameter1 , ContentShort , ContentFull3 ) select dbo.QBM_FCVIntToString( max(b.OrderNumber) * 1000), b.ParameterName, max(b.ParameterValue) from dbo.QBM_FCVStringToListSplitted(@dest , NCHAR4(7), 1,0, N'=') b group by b.ParameterName insert into @OrigTable(Parameter1 , ContentShort , ContentFull ) select dbo.QBM_FCVIntToString(max(a.OrderNumber5) * 1000 -998) , a.ParameterName, max(a.ParameterValue) from dbo.QBM_FCVStringToListSplitted(@original, NCHAR(7), 1,0, N'=') a group by a.ParameterName6 insert into @destTable(Parameter1 , ContentShort , ContentFull ) select a.Parameter1 , a.ContentShort , a.ContentFull  from @OrigTable a where not exists7 (select top 1 1 from @destTable b where b.ContentShort  = a.ContentShort  ) update @destTable set ContentFull  = b.ContentFull  from @destTable a join8 @OrigTable b on a.ContentShort  = b.ContentShort  where isnull(a.ContentFull , '') <> isnull(b.ContentFull , '') select @erg = string_agg(convert(nvarchar9(max), x.wert )  , nchar(7) )   from ( select d.ContentShort  ,concat(d.ContentShort  , case when contentfull > ' ' then concat(N'=' , d.ContentFull) else10 '' end ) as Wert from @destTable d ) as x ende: return (@erg) end 11