Raymond Lewallen

Framework Design, Agile Coach, President Oklahoma City Developers Group, Microsoft MVP C#, TDD, Continuous Integration, Patterns and Practices, Domain Driven Design, Speaker, VB.Net, C# and Sql Server

SQLCLR UDF Returns a Truncation Exception

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.

Published Jun 13 2005, 06:03 AM by Raymond Lewallen

Comments

No Comments

Name (required)

Your URL (optional)

Comments (required)

Remember Me?

Enter the numbers above:

Add

About Raymond Lewallen

Working primarily in the public sector during his career, Raymond has designed and built several high profile enterprise level applications for all levels of the government. Raymond now works as a solutions architect for EMC. Raymond is an agile coach, Microsoft MVP C# and also president of the Oklahoma City Developers Group and Oklahoma Agile Developers Group. Raymond spends a lot of his time learning and teaching such things as Test Driven Development, Domain Driven Design, Design Patterns and Extreme Programming practices and principles, to name a few. Raymond is also an advocate of Alt.Net. Raymond is primarily a framework guy, so don't ask him anything about UI :)