question

GGunnfs-5814 avatar image
0 Votes"
GGunnfs-5814 asked TimonYang-MSFT commented

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

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?
sql-server-generaldotnet-csharp
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
1 Vote"
Viorel-1 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TimonYang-MSFT avatar image
0 Votes"
TimonYang-MSFT answered TimonYang-MSFT commented

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.

135497-capture.png


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.


capture.png (12.9 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@GGunnfs-5814
I have not heard from you for a couple of days. Please let me know if there is anything that I can help here.

0 Votes 0 ·