I have defined two functions
1) Returns String
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString TestScalarFunction()
{
return new SqlString ("RJ");
}
2) Returns Datetime
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlDateTime ScalarUDF()
{
string zoneId = "Singapore Standard Time";
TimeZoneInfo tzi = TimeZoneInfo.FindSystemTimeZoneById(zoneId);
SqlDateTime result = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, tzi);
return result;
}
After creating the UDF as::
CREATE FUNCTION [dbo].TestScalarFunction()
RETURNS nvarchar(100)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME UDF_Blog.UserDefinedFunctions.TestScalarFunction;
GO
CREATE FUNCTION [dbo].ScalarUDF()
RETURNS datetime
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME UDF_Blog.UserDefinedFunctions.ScalarUDF;
GO
I am Getting Problem with only UDF(ScalarUDF) which returns Datetime in its result.
I am using query for both as::
select dbo.TestScalarFunction() --this successfully gives Result
select dbo.ScalarUDF() --this gives the Error