Create SQL functions with parameters

Question

Hello,

I have a lot of sql scripts which use functions like "string_agg" and "concat_ws". those funcations are available from sql 2017.
now I would like to create some backward compatibility for MSSQL 2016 and earlier.

I tried if I just can include a newer .dll in sql 2016, but I cannot find that.
So I tried creating a manual fucntion like this.
public static SqlString Concat_Ws(SqlString separator, params SqlString[] expression)
however when I try to include this in sql with this command
CREATE FUNCTION [dbo].[ConCat_Ws](@separator nvarchar(4000), @expression nvarchar(4000))
RETURNS nvarchar(4000)
AS EXTERNAL NAME [StringUtils].[ConcatWs].[Concat_Ws];
GO

I get an error

Msg 10318, Level 16, State 1, Procedure ConCat_Ws, Line 2 [Batch Start Line 41]

'CREATE FUNCTION' failed because parameter 2 of method 'Concat_Ws' of type 'ConcatWs' is annotated with unsupported attribute System.ParamArrayAttribute.
because of the params. And I cannot get it to work.
can someone please point me in de correct direction?

Thank you.

Answer 1

User-defined functions in SQL Server has a fixed number of parameters. There is no provision to create a function that takes a variable number of parameters. So the best you could do is to create a CREATE_WS which takes, say, six strings plus the separator. But then you need to always specify all six string parameters every time you use it, since T-SQL does not permit you to leave out parameter to functions - even if they have default values.

When it comes to string_agg, you can implement your own CLR aggregate, but there is no way you can sneak in the WITHIN GROUP clause.

Answer 2

Hi @Bart K ,

As mentioned by Erland, you should fix your parameters when you create one User-defined function.

You could also check whether there is any 'BEGIN' or 'END' missing in your procedure.

Per my knowledge, you could simplely use "STUFF(COLUMN FOR XML PATH('')" instead of "string_agg" and use "CONCAT" instead of "concat_ws" on MSSQL 2016 and earlier.

Please refer one example from below:

DECLARE @A_TEMP_TABLE_USED_AS_EXAMPLE TABLE(CountryCode INT, City VARCHAR(50))  
  
INSERT @A_TEMP_TABLE_USED_AS_EXAMPLE(CountryCode, City) VALUES  
(1, 'Johannesburg'), (1, 'Cape Town'), --South Africa  
(2, 'New York'), (2, 'Washington'), --USA  
(3, 'Paris') ,(3, 'Nice'), --France  
(4, 'Rome'), (4, 'Bologna'), --Itlay  
(5, 'Athens'), (5, 'Volos') --Greece  
  
SELECT    
 CountryCode,  
 STRING_AGG(city,',')  
 FROM @A_TEMP_TABLE_USED_AS_EXAMPLE   
 GROUP BY CountryCode  
  
SELECT DISTINCT   
 CountryCode,  
 STUFF((SELECT ',' + City  
 FROM @A_TEMP_TABLE_USED_AS_EXAMPLE [TABLE1]  
 WHERE TABLE1.CountryCode = TABLE2.CountryCode  
 FOR XML PATH('')), 1, 1,'')   
 FROM @A_TEMP_TABLE_USED_AS_EXAMPLE [TABLE2]   
  
SELECT DISTINCT  CONCAT_WS(',',CountryCode,City)  
 FROM @A_TEMP_TABLE_USED_AS_EXAMPLE   
  
SELECT DISTINCT   
 CONCAT(CountryCode,',',City)  
 FROM @A_TEMP_TABLE_USED_AS_EXAMPLE  

If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Melissa