Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

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.

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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