How to access external DLL's static methods that incorporate output params in SQL Server

Question

I know how to load a CLR DLL in SQL Server and access to its static methods which does not incorporate output parameters in their method signatures but what I cannot realize is how I can use methods which have output parameters.

For example it is strightforward to access Math.dll 's factorial method in SQL server like this:

CREATE ASSEMBLY MathAsm FROM 'C:\Programming\c#\SQLCLRIntegrationExample\assemblies\Math.dll'
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
Go

CREATE FUNCTION Factorial(@x FLOAT)
    RETURNS FLOAT
    EXTERNAL NAME MathAsm.Math.Factorial;
Go

But what if method's signature would be like:

public static int GetInfo(int[] inputParams, out int[] outputParams)

Thanks in advance

Roman Pokrovskij · Answer 1 · 2014-02-06T11:07:54.743

There are restrictions on method's signature. Have you read this:

http://technet.microsoft.com/en-us/library/ms131092.aspx ?

It is possible to have OUT parameters, but int[] out - upss. Also note that only SP could have output parameters.

So to access such methods you need to write your function, into something like this:

public SqlInt GetInfo(SqlXml inputParamsXml, out SqlXml  ouputParamsXml)
{
   var inputParams = MyXmlToArrayFunc(inputParamsXml);
   int[] outputParams;
   var r= YourModule.GetInfo(inputParams, out outputParams);
   ouputParamsXml = MyArrayToXmlFunc(outputParams);
   return new SqlInt(r);
}

How to access external DLL's static methods that incorporate output params in SQL Server

1 Answers1