views:

165

answers:

1

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' ;
A: 
Triynko