This post originated from an RSS feed registered with .NET Buzz by Raymond Lewallen. Original Post: SQLCLR UDF Returns a Truncation Exception Feed Title: Raymond Lewallen Feed URL: /error.htm?aspxerrorpath=/blogs/raymond.lewallen/rss.aspx Feed Description: Patterns and Practices, OOP, .Net and Sql Latest .NET Buzz Posts Latest .NET Buzz Posts by Raymond Lewallen Latest Posts From Raymond Lewallen

So I have this SQLCLR UDF that returns my name, “Raymond Lewallen”, that I use for testing purposes.  I was changing some code to work with the latests bits of VS2K5, and discovered something that has changed.

Lets say I had this in Sql Server:

Sql function, from .Net assembly, that returns my name

CREATE FUNCTION GetName()
RETURNS NVARCHAR(20)
AS EXTERNAL NAME IdentityAssembly.PersonalInfo.MyName

That returns “Raymond Lewallen”, as expected.  But now let us change the length of the return value.

Sql function, from .Net assembly, that returns my name

CREATE FUNCTION GetName()
RETURNS NVARCHAR(10)
AS EXTERNAL NAME IdentityAssembly.PersonalInfo.MyName

Now, prior to the April CTP release of Sql2k5, this would return “Raymond Le”.  You get data that has been silently truncated.  That was the expected behavior.  But now, after installing the April CTP, I no longer get truncated data.  Now I get a ‘Truncation Exception’ reported.

However, if I do this:

T-Sql function that returns my name

 CREATE FUNCTION GetName()
RETURNS NVARCHAR(10)
AS
BEGIN
  RETURN N'Raymond Lewallen'
END

 It still returns “Raymond Le”.  The T-Sql still silently truncates the data.  Only the SQLCLR UDF returns the truncation exception.  So how you expect your functions to behave, whether they return truncated data or exceptions when data is going to be trunctated, may dictate how and where you write your functions.