SQL Server CLR UDF with Out Parameters - Is It Possible?
c#, clr, sql-server, sqlclr, user-defined-functions
Solution
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.
Problem
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?