Sunday, July 8, 2007

T-SQL and CLR types do not match

As you know in SQL Server 2005 you can reference to .NET classes doing some operations that in T-SQL is realy hard to perform. For example if you need to format very long and 'complicated' string value , so by using .NET String method you can easily achive it. But I'd like to show you another example actually posted by Itzik Ben-Gan on his website ,so I just modified his C# code to VB.NET that is invoking the ToString method of the SqlDateTime datatype with a format string.

Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Public Class Valid
"<"SqlFunction(DataAccess:=DataAccessKind.Read, IsDeterministic:=True)">" _
Public Shared Function fn_format_datetime(ByVal dt As SqlTypes.SqlDateTime, _
ByVal formatstring As SqlTypes.SqlString) As String
If dt.IsNull Or formatstring.IsNull Then
Return formatstring.IsNull()
Else
Return dt.Value.ToString(formatstring.Value)
End If

End Function

End Class

I assume you are familiar with .NET technology , so I build a solution and try to reference to this function within my database.I created ASSEMBLY without problem but when I tried to create a function I got the error "CREATE FUNCTION for "fn_format_datetime1" failed because T-SQL and CLR types for return value do not match."

CREATE ASSEMBLY dateformat1 FROM 'C:\dateformat1 .dll'
with permission_set = SAFE
CREATE FUNCTION fn_format_datetime(@dt as datetime,
@formatstring as VARCHAR(100))RETURNS VARCHAR(100)
AS EXTERNAL NAME dateformat1.[dateformat1.classname].fn_format_datetime

As you see T-SQL datatype VARCHAR(n) does not match .NET STRING class. I should use NVARCHAR(n) datatype to get it working, did not know it.

CREATE ASSEMBLY dateformat1 FROM 'C:\dateformat1 .dll'
with permission_set = SAFE
CREATE FUNCTION fn_format_datetime(@dt as datetime,
@formatstring as NVARCHAR(4000))RETURNS NVARCHAR(4000)
AS EXTERNAL NAME dateformat1.[dateformat1.classname].fn_format_datetime

--Usage
SELECT
dbo.fn_format_datetime(GETDATE(), 'yyyyMMdd'),
dbo.fn_format_datetime(GETDATE(), 'yyyymmdd');

1 comment:

aiya said...
This comment has been removed by a blog administrator.