2

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)
2

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.

  • Thanks Jon, your answer was really helpful because I did not have a clue – Farshid Jan 29 '14 at 17:07
  • @Farshid: Okay, no problem. You're welcome. – Jon Seigel Jan 29 '14 at 17:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.