Implement OnNullCall property in SqlFunctionAttribute for RETURNS NULL ON NULL INPUT SQLCLR
The CREATE FUNCTION statement allows for specifying "WITH RETURNS NULL ON NULL INPUT" that, if set, causes the function to return NULL and not get executed if any input parameter is set to NULL. This is a wonderful and important option, especially with SQLCLR functions that accept SqlString input parameters since their value is transferred to the AppDomain upon being executed. This is a waste of CPU and memory if you know that the function will simply exit and not make use of that string value.
Unfortunately, when using SSDT, there is no way to set this option in the source code. The only way to accomplish this is to issue an ALTER FUNCTION statement in a Post-Deployment SQL Script. But that is a brittle solution as it will break if changes are made to the function name, parameter names and/or datatypes, or the number of parameters. Hence it is one more thing to maintain.
Upvotes: 3<=-=Feb 16 2016 2:53PM=-=>
Hi Solomon. Taking a look at your issue and proposed solution, it is technically difficult to add the attribute into that class, particularly given that this would be done in a newer version of .Net, which when built would not be supported on older versions of SQL Server. Based on this, we intend to close out this issue as “won’t fix”.
Note that there is a workaround to this issue, though it’s not ideal.
- Copy the contents of the .generated.sql file under the output folder which has the SQL definitions of the functions and other SQLCLR types you use
- Go to the SQLCLR tab in Project Properties and uncheck “Generate DDL”. This will stop using this generated file, or updating it with new type info
- Add the contents of the file back into a regular script file in the project system, and update to use your desired syntax.
- From now on, new functions/changes to function signature would need to be managed by you as we won’t auto-gen.
Thanks for raising this issue.
SQL Server tools team
Hi Kevin and thanks for responding. I am a little confused, however, regarding the part about it not being supported on older versions of SQL Server. What exactly wouldn’t be supported? I thought that the “WITH RETURNS NULL ON NULL INPUT” option has been available for SQLCLR scalar functions since CLR Integration was introduced in SQL Server 2005. I don’t see how the version of .NET matters (for the most part) since this property of the SqlFacetAttribute is only read by the SSDT “generate script” process. It is not a property that the Database Engine will see / interpret, unlike the SqlMethodAttribute.OnNullCall property, which needs to be handled by the Database Engine since there is no exposed object definition to hold this property (i.e. sys.sql_modules.null_on_null_input).
Am I missing something, or did I confuse things by using the term “OnNullCall” in the title?
Solomon Rutzky commented
I originally submitted this suggestion via: