Background:
Visual studio fails at deploying a database project. It tries to drop functions that are already referenced (e.g. in a check constraint), rather than just adding the new ones and updating the existing ones, so the deployment always fails.
As a result, I'm writing my own code to update the assembly and add/update any functions.
I assume the compiler/deployer uses reflection and properties of the SqlFunction attribute, so I'm also using reflection to gather a MethodInfo list of the static methods that have the SqlFunction attribute.
Question/Task:
I need to know how to translate the SqlFunctionAttribute's properties (e.g. IsDeterministic, DataAccess, Name, IsPrecise, etc.) and the method signature of the function into an appropriate T-SQL "CREATE FUNCTION" statement.
Existing information I've already found to be not helpful:
The documentation for 'create function' is confusing and incomplete. Towards the bottom it finally mentions some of the SqlFunction properties like IsDeterministic, but it talks about them like they're C# properties, not T-SQL parameters, so I have no idea how to use them in a create function statement.
//CLR Functions
CREATE FUNCTION [ schema_name. ] function_name
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type
[ = default ] }
[ ,...n ]
)
RETURNS { return_data_type | TABLE <clr_table_type_definition> }
[ WITH <clr_function_option> [ ,...n ] ]
[ AS ] EXTERNAL NAME <method_specifier>
[ ; ]
I would expect the clr_function_option
parameter to handle things like IsDeterministic, but it's not listed as an option.
Meanwhile, in documentation for IBM DB2, I see statements like the following, which the MSDN documentation has nothing remotely similar:
CREATE FUNCTION countUp(INTEGER)
RETURNS INTEGER
LANGUAGE CLR
PARAMETER STYLE SQL
SCRATCHPAD 10
FINAL CALL
NO SQL
FENCED
THREADSAFE
NOT DETERMINISTIC
EXECUTION CONTORL SAFE
EXTERNAL NAME 'gwenUDF.dll:bizLogic.empOps!CountUp' ;