what did I miss in the c# sql server Function during create function?

Question

for the created assembly Util
I tried to execute:
CREATE FUNCTION "dbo"."$formatListNormalized"(@list VARCHAR(max) )
RETURNS VARCHAR(max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME Util.Util.formatListNormalize
GO

I got the message

Msg 6551, Level 16, State 2, Procedure $formatListNormalized, Line 1

CREATE FUNCTION for "$formatListNormalized" failed because T-SQL and CLR types for return value do not match.

and the corresponding clr function member is

[SqlFunction]
public static class Util
{
 ...
 [SqlFunction]
 public static string formatListNormalize(string list)
 {
 if (list == null) return null;
 list = list.Trim();
 if (list.Length < 1) return null;
 list = Regex.Replace(list, @"(?<comma>\s*[,]\s*)", ",").Trim();
 if (list.EndsWith(",")) list = list.Substring(0, list.Length - 1); /// 150821 added to remove trailing comma
 if (list.StartsWith(",")) list = list.Substring(1, list.Length - 1); /// 
 return list;
 }
 ...
}

I thought sql equivalent of c# string is varchar(max). why am I getting the return type mismatch error?

Answer 1

Try nvarchar(max) for input and output types, because varchar does not seem supported: https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration-database-objects-user-defined-functions/clr-scalar-valued-functions?view=sql-server-ver15#requirements-for-clr-scalar-valued-functions.

Answer 2

String in C# only corresponds to nchar and nvarchar in Sql Server.

I reproduced your problem using varchar and then switched to one of them and the problem disappeared.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.