Pass table as parameter to SQLCLR TV-UDF
- by Skeolan
We have a third-party DLL that can operate on a DataTable of source information and generate some useful values, and we're trying to hook it up through SQLCLR to be callable as a table-valued UDF in SQL Server 2008.
Taking the concept here one step further, I would like to program a CLR Table-Valued Function that operates on a table of source data from the DB.
I'm pretty sure I understand what needs to happen on the T-SQL side of things; but, what should the method signature look like in the .NET (C#) code? What would be the parameter datatype for "table data from SQL Server?"
e.g.
/* Setup */
CREATE TYPE InTableType
AS TABLE (LocationName VARCHAR(50), Lat FLOAT, Lon FLOAT)
GO
CREATE TYPE OutTableType
AS TABLE (LocationName VARCHAR(50), NeighborName VARCHAR(50), Distance FLOAT)
GO
CREATE ASSEMBLY myCLRAssembly
FROM 'D:\assemblies\myCLR_UDFs.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
CREATE FUNCTION GetDistances(@locations InTableType)
RETURNS OutTableType
AS
EXTERNAL NAME myCLRAssembly.GeoDistance.SQLCLRInitMethod
GO
/* Execution */
DECLARE @myTable InTableType
INSERT INTO @myTable(LocationName, Lat, Lon) VALUES('aaa', -50.0, -20.0)
INSERT INTO @myTable(LocationName, Lat, Lon) VALUES('bbb', -20.0, -50.0)
SELECT * FROM @myTable
DECLARE @myResult OutTableType
INSERT INTO @myResult
MyCLRTVFunction @myTable --returns a table result calculated using the input
The lat/lon - distance thing is a silly example that should of course be better handled entirely in SQL; but I hope it illustrates the general intent of table-in - table-out through a table-valued UDF tied to a SQLCLR assembly.
I am not certain this is possible; what would the SQLCLRInitMethod method signature look like in the C#?
public class GeoDistance
{
[SqlFunction(FillRowMethodName = "FillRow")]
public static IEnumerable SQLCLRInitMethod(<appropriateType> myInputData)
{
//...
}
public static void FillRow(...)
{
//...
}
}
If it's not possible, I know I can use a "context connection=true" SQL connection within the C# code to have the CLR component query for the necessary data given the relevant keys; but that's sensitive to changes in the DB schema. So I hope to just have SQL bundle up all the source data and pass it to the function.
Bonus question - assuming this works at all, would it also work with more than one input table?
