How to access external DLL’s static methods that incorporate output parameters in SQL Server

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:Programmingc#SQLCLRIntegrationExampleassembliesMath.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)

What have you tried and what error are you getting?

The following works for me on SQL Server 2008 R2.

C#:

namespace CLRTest
{
    public static class ParamsTest
    {
        [SqlProcedure]
        public static void Test(out int p)
        {
            p = 5;
        }
    }
}

SQL:

CREATE PROCEDURE dbo.Test(@p int OUTPUT)
    AS EXTERNAL NAME [CLRTest].[CLRTest.ParamsTest].[Test];
GO


DECLARE @x int;

EXEC dbo.Test @p = @x OUTPUT;

SELECT @x;

Returns 5 as expected.

Now, if you’re trying to return an array in an output parameter, I don’t think that’s supported, save for the exceptions noted here. If that’s what you’re looking to do, use a table-valued function or stored procedure to return a result set. Note also that table-valued (input) parameters are not supported as mentioned here, assuming you’re using SQL Server 2008 or higher.