How to access external DLL's static methods that incorporate output parameters 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)

Answer 1

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.

• Farshid: Thanks Jon, your answer was really helpful because I did not have a clue