SQL Server CLR UDF with Out Parameters - Is It Possible?

Question

Is there a way to create a CLR User-Defined Function for SQL Server which returns multiple values without using Table Valued Function syntax? For example, say I want to perform a coordinate conversion such as:

[SqlFunction()]
public void ConvertCoordinates(SqlDouble x, SqlDouble y, SqlDouble z, out SqlDouble r, out SqlDouble t, out SqlDouble p)
{
   r = new SqlDouble(Math.Sqrt((x.Value*x.Value)+(y.Value*y.Value)+(z.Value*z.Value)));
   t = new SqlDouble(Math.Acos(r.Value / z.Value));
   p = new SqlDouble(Math.Atan(y.Value / x.Value));
}

Is this even possible? A table-valued function in this case seems inappropriate because the computation will never yield more than one output row. Using scalar valued function syntax, I would have to write three different functions to perform the computation and call each separately. Given my actual use case, this is highly impractical.

I realize that the above logic can be accomplished using pure T-SQL; my actual use case is more complex but would still only result in a single row having multiple interdependent output values.

So, bottom line, is it feasible? I don't think it is, but one can hope. If by chance it is feasible, then what would the T-SQL look like that calls such a function?

Answer 1

Since you've already taken the CLR plunge, you could create your own CLR type with which you can do all sorts of crazy stuff. As a practical example, the native spatial types are implemented this way, as is HierarchyID. Once you've defined your own type, you can have your function return it. If getting at individual components (if I may presume in your case radius, theta, and phi), create methods on the type that return such.

Answer 2

This is an older question and I found it while searching for an answer to a very similar question.

SQL Functions allow for a return value but do not allow the use of an output parameter.

SQL Procedures do allow output parameters (but not a return value)

I found this from the MSDN Example here:

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

 [Microsoft.SqlServer.Server.SqlProcedure]
 public static void PriceSum(out SqlInt32 value)
 { … }

and

CREATE PROCEDURE PriceSum (@sum int OUTPUT)
AS EXTERNAL NAME TestStoredProc.StoredProcedures.PriceSum

When I switched from trying to register a Function to a Procedure everything worked out. It should be pointed out that a Function can have a return value but a Stored Procedure can not. In my case I was returning a string value and had output params for some optional detailed information like status codes, etc. All I did was change my method to be a void and passed the string value back as an output param.

Your method is a void anyways and has no return value and as such is a perfect candidate for using a Stored Procedure instead of a Function.