I have created a new function in an assembly which has been successfully added as a CLR Assembly to my SQL server. Now I am trying to create a SQL user defined function to map to the new method in that assembly. I have other methods in the assembly which have been mapped successfully.
Here is my function
CREATE FUNCTION [dbo].[FromCamelCase](@value [nvarchar(MAX)])
RETURNS [nvarchar(MAX)] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Tools_CLR].[UserDefinedFunctions].[FromCamelCase]
GO
On execute, I am getting this error.
Msg 15151, Level 16, State 1, Procedure FromCamelCase, Line 2
Cannot find the type 'nvarchar(MAX)', because it does not exist or you do not
have permission.
I have tried changing the type from nvarchar
to just varchar
. I have also tried changing the size from MAX
to 4000 and also just 50.
Here is my C# method, for reference.
[Microsoft.SqlServer.Server.SqlFunction]
public static string FromCamelCase(string val)
{
if (val == null) return string.Empty;
val = val.Replace("_", "");
StringBuilder sb = new StringBuilder(val.Length + 10);
bool first = true;
char lastChar = '\0';
foreach (char ch in val)
{
if (!first && (char.IsUpper(ch) || char.IsDigit(ch) && !char.IsDigit(lastChar)))
sb.Append(' ');
sb.Append(ch);
first = false;
lastChar = ch;
}
return sb.ToString();
}
Remove the brackets from around the data type name: RETURNS NVARCHAR(MAX)
. The double brackets mean it's interpreted as a user-defined data type called "NVARCHAR(MAX)" (one word) and quoted because of the parentheses in the name.