I'm just learning to use the new CLR integration features of SQL 2005 and I
created the following simple function:

    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true,
SystemDataAccess=SystemDataAccessKind.None)]
    public static SqlString Foo(DateTime dtStartDate, DateTime dtEndDate)
    {
         return new SqlString("Hello, World!");
    }

Everything works correctly until I pass a NULL value to the function.  So, I
changed the function to accept Nullable parameters as follows:

    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true,       
SystemDataAccess=SystemDataAccessKind.None)]
    public static SqlString DateDiff(Nullable<DateTime> dtStartDate,
Nullable<DateTime> dtEndDate)
    {
         return new SqlString("Hello, World!");
    }

Everything compiles correctly, but when I attempt to deploy the assembly I
receive the following error:

Error    1    Incorrect syntax near '`'.

If I remove the Nullable<> option, everything works correctly.

Anyone have any input on this?

Thanks...

Show quote Hide quote Inside SQL Server use the SQL Server native types for nullability support.

  [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true,
SystemDataAccess=SystemDataAccessKind.None)]
  public static SqlString Foo(SqlDateTime dtStartDate, SqlDateTime
dtEndDate)
  {
    if (dtStartDate.IsNull || dtEndDate.IsNull)
    {
      return SqlString.Null;
    }
    DateTime start = dtStartDate.Value;
    DateTime end = dtEndDate.Value;


    return new SqlString(end.ToString);
  }


David

=?Utf-8?B?SS9HZWFy?= <IG***@discussions.microsoft.com> wrote in
Show quoteHide quoteAs David replies in his post; you are using CLR's DateTime type, which
is a value type and not nullable. Change instead to use (as David says)
the SqlDateTime type. They live in the SqlTypes namespace and they are
nullable and also completely isomorphic with the underlying T-SQL types,
i.e. they need no converssion so you'll get better performance out of
them (probably not noticeble, but still). In fact, when writing SQLCLR
methods you should always use SqlTypes.


That's because the deployment functionality does not understand the new
nullable types.

Niels

CHARINDEX in CASE Within SELECT Statement
Default Value Calculation
Deadlock updating different rows
No retrieving dupicate rows
Service Broker - BEGIN DIALOG CONVERSATION @dialog_handle