SqlDecimal Function Parameters With NULL Values

Hi,

I have a CLR function that throws an error if one of the parameters is NULL. Am I using the IsNullable tag correctly or am I supposed to do this another way? The function simply formats decimal values using .NET culture information and returns a string. Thanks very much for any help. -- Erik


[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true, DataAccess=DataAccessKind.None)]

[return: SqlFacet(MaxSize = 30, IsNullable=true)]

public static string FormatGeneralDecimal([SqlFacet(Precision = 28, Scale = 8, IsNullable = true)] SqlDecimal sqlDc,

[SqlFacet(MaxSize = 10)] string cultureName)

{


string result = null;

if (!sqlDc.IsNull)

{


CultureInfo ci = CultureInfo.CreateSpecificCulture(cultureName);

result = sqlDc.Value.ToString("G", ci);

}


return result;

}

It works great unless I call it with a NULL value for sqlDc, in which case I get this:


select [dbo].[FormatGeneralDecimal](NULL, 'de-DE')



Msg 6522, Level 16, State 2, Line 1

A .NET Framework error occurred during execution of user-defined routine or aggregate "FormatGeneralDecimal":

System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

System.Data.SqlTypes.SqlNullValueException:

at System.Data.SqlTypes.SqlDecimal.ToDecimal()

at System.Data.SqlTypes.SqlDecimal.get_Value()

at MyFunctions.FormatGeneralDecimal(SqlDecimal sqlDc, String cultureName)