SQLCLR Nullable DATETIME2 in SSDT

Question

I want to write an SQLCLR UDF that takes a DATETIME2 and returns a DATETIME2. The input and output should allow NULLs.

I create an SQL Server Database Project (SSDT), configure it as VB language in its SQLCLR properties, and then add the following file Test.vb:

Option Explicit On
Option Strict On

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions
    <SqlFunction()> _
    Public Shared Function Test(d As Nullable(Of DateTime)) As Nullable(Of DateTime)
        Return d
    End Function
End Class

The use of nullable in this way appears like it has been supported since SQL Server 2008 per http://msdn.microsoft.com/en-us/library/ms131092(v=SQL.100).aspx.

However, when I run the deploy command, I get the following error:

SQL46010: Incorrect syntax near ).

This is because the SQL it generated was:

CREATE FUNCTION [dbo].[Test] (@d /* Error: Unsupported type. */)
RETURNS /* Error: Unsupported type. */
    AS EXTERNAL NAME [Test].[Test.UserDefinedFunctions].[Test];

I cannot substitute SqlDateTime because I require the full range and precision of DATETIME2.

Answer 1

Take a look at this. SqlDateTime allows nulls hence the ability to check if the value is null through the class' property.

Answer 2

The problem is with SSDT (SQL Server Data Tools) and not with SQLCLR. Yes, SQLCLR does indeed support DATETIME2 via DateTime? / Nullable<DateTime>. Unfortunately, SSDT (I am using VS2013 and SSDT v 12.0.50512.0) does not yet (I am being optimistic here, I know) support inferring DATETIME2 from either DateTime or DateTime?. But, it also doesn't error when using DateTime? like it used to. Still, either DateTime or DateTime? will show up as a regular DATETIME in the generated SQL.

I am not sure of any truly "appropriate" means of telling SSDT what the datatype should be. There are actually quite a few options that are not supported, including general UDF options such as WITH RETURNS NULL ON NULL INPUT and parameter options such as default values. It is sad and frustrating, yes.

The best I have come up with so far (and I am still looking into other options) is to add a Post Deployment script to ALTER the definition of the function with the desired options:

1. In the PROJECT menu, select Add New Item... (Control+Shift+A in VS2013)
2. Go to SQL Server -> User Scripts
3. Select Post-Deployment Script
4. Give it a name (the name itself doesn't determine if it is pre-deploy, post-deploy, or neither; it just needs to end in .sql)and click Add
5. You will be placed into a (mostly) empty SQL script

6. Enter in one or more ALTER statements, similar to the following:

   -- declare once
   DECLARE @ObjectName sysname; -- keep lower-case to work in case-sensitive collations
   
   SET @ObjectName = N'Test';
   
   IF (EXISTS(
               SELECT  *
               FROM    sys.assembly_modules sam
               WHERE   sam.[object_id] = OBJECT_ID(@ObjectName)
           )
       )
   BEGIN
       PRINT 'Checking custom properties for [' + @ObjectName + N']...';
   
       IF (EXISTS(
                   SELECT  *
                   FROM    sys.parameters sp
                   INNER JOIN  sys.types st
                           ON  st.system_type_id = sp.system_type_id
                   WHERE   sp.[object_id] = OBJECT_ID(@ObjectName)
                   AND     st.[name] <> N'datetime2' -- keep lower-case to work in
                                                     -- case-sensitive collations
               )
       )
       BEGIN
           PRINT 'Setting custom properties for [' + @ObjectName + N']...';
   
           BEGIN TRY
               EXEC('
   ALTER FUNCTION [dbo].[Test](@d [datetime2])
   RETURNS [datetime2] WITH EXECUTE AS CALLER
   AS EXTERNAL NAME [Test].[Test.UserDefinedFunctions].[Test];
               ');
           END TRY
           BEGIN CATCH
               DECLARE @ErrorMessage NVARCHAR(4000);
               SET @ErrorMessage = ERROR_MESSAGE();
               RAISERROR(@ErrorMessage, 16, 1);
               RETURN;
           END CATCH;
       END;
   
   END;
   ELSE
   BEGIN
       RAISERROR(N'Oops. [%s] was renamed or no longer exists!', 16, 1, @ObjectName);
       RETURN;
   END;
   
   ---
   
   SET @ObjectName = N'NextObjectToFix';
   -- copy the rest from above
   

This Post Deployment script will always be included at the end of both the _Create and publish / incremental build scripts. Hence the extra logic to see if the changes are already present or not. True, it generally doesn't hurt to always run the ALTER, but in the rare case that this object is a dependency of something else, such as a Check Constraint or Computed Column, it's probably best to just leave it alone unless it needs to change.

You can get the proper ALTER definition either from the \bin\Configuration\*_Create.sql script (just change the CREATE to ALTER), or in SSMS if you right-click on the object and select Modify. In both cases, change the datatype and any other options (obviously ;-).

---

Another, somewhat related, idea is to simply not rely upon the Visual Studio / SSDT publishing process for the T-SQL wrapper object CREATE statements, and only use it to manage the assembly. In this setup you would un-check the Generate DDL option on the SQLCLR tab of Project Properties. Then you would add a Post Deployment script (as noted in the suggestion above) and put in your own CREATE FUNCTION ..., CREATE PROCEDURE ..., etc statements.

For initial development this isn't as quick and easy for introducing a new object than using SSDT to generate that DDL, but given that new objects are not created terribly often, and that their signatures are changed far less frequently, managing this DDL yourself really isn't so bad (and in fact, it is very similar to the process that I use for my SQL# library which has over 250 objects. Pragmatically speaking, once you have one of each object type CREATE statement, you can just copy and paste them for new objects and change the names and parameters, etc. The most work this approach ever requires is when creating a new TVF if it returns a bunch of fields, but that isn't truly that much work given you would have to enter that same into into the TableDefinition property of the SqlFunction attribute anyway if you were using SSDT to manage the DDL creation.