CLR Table Valued Function Parameter Count for FillRow method error

Question

I have a C# (.NET Framework 4.7.2) script to perform some custom reading of trace codes, which I am trying to use to make a SQL (V14.0.3445.2) Table Valued Function. The C# FillRow Method looks like this:

public partial class UserDefinedFunctions
{
    [SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "Read_Trace",
    TableDefinition =
    " Site NVARCHAR(3)" +
    ",Line TINYINT" +
    ",Shift NVARCHAR(2)" +
    ",ProductionDate DATETIME2(0)" +
    ",ProductionTime NVARCHAR(5)" +
    ",DayName NVARCHAR(9)" +
    ",Perfect_Trace BIT" +
    ",NWFGenerated_Trace BIT" +
    ",Altered_Trace BIT")]
public static IEnumerable Read_Trace([SqlFacet(MaxSize = 255)] SqlChars customer, [SqlFacet(MaxSize = 255)] SqlChars trace)
{
    var trc = trace.ToString();
    RegexList regexList = GetRegexList(customer.ToString(), trace.ToString());

    return new List<TraceContents>
    {
        new TraceContents
        {
            Site = GetValue(regexList.Site_Regex, trc),
            Line = SqlInt16.Parse(new string(GetValue(regexList.Line_Regex, trc).Where(char.IsNumber).ToArray())),
            Shift = GetValue(regexList.Shift_Regex, trc),
            ProductionDate = JulianToDate(GetValue(regexList.Date_Regex, trc)),
            ProductionTime = GetValue(regexList.Time_Regex, trc),
            DayName = GetValue(regexList.Day_Regex, trc),
            Perfect_Trace = regexList.Perfect_Trace,
            NWFGenerated_Trace = regexList.NWFGenerated_Trace,
            Altered_Trace = regexList.Altered_Trace
        }
    };
}

I have the following SQL Script to make that into a function:

DROP ASSEMBLY if exists CLR_Functions;
CREATE ASSEMBLY CLR_Functions
FROM 'C:\Temp\Functions.dll'
GO

CREATE FUNCTION fn_ReadTraceCode(@InvoiceAccount NVARCHAR(255), @Trace NVARCHAR(255)) RETURNS
TABLE (
     [Site] NVARCHAR(3)
    ,[Line] TINYINT
    ,[Shift] NVARCHAR(2)
    ,[ProductionDate] DATETIME2(0)
    ,[ProductionTime] NVARCHAR(5)
    ,[DayName] NVARCHAR(9)
    ,[Perfect_Trace] BIT
    ,[NWFGenerated_Trace] BIT
    ,[Altered_Trace] BIT)
AS EXTERNAL NAME CLR_Functions.UserDefinedFunctions.Read_Trace
GO

The number of parameters match for both of these scripts match, which I thought was the requirement, but when I run the SQL Script, I get the following error:

Msg 6208, Level 16, State 1, Procedure fn_ReadTraceCode, Line 2 [Batch Start Line 4] CREATE FUNCTION failed because the parameter count for the FillRow method should be one more than the SQL declaration for the table valued CLR function.

I don't understand what I have done wrong - I have followed through step by step guides (like this) and have not found anything about an additional parameter needed, and in fact have once made a CLR function before that didn't seem to need an additional parameter... The only online answer to the question refers to an incident in which there was a genuine mismatch between the return columns of the function, compounding my confusion.

Can you help me to stop the error appearing, so my function compiles? I can post complete code if needed, but I think the problem will be with the FillRow method listed above.

Answer 1

Your code simply doesn't match what SQLCLR is expecting. If you look at the docs, you need two functions: one to return an IEnumerable, and one which fills the row and has a bunch of out parameters to do that.

[SqlFunction(
    DataAccess = DataAccessKind.Read,
    FillRowMethodName = nameof(Read_Trace_Fill),
    TableDefinition =
    " Site NVARCHAR(3)" +
    ",Line TINYINT" +
    ",Shift NVARCHAR(2)" +
    ",ProductionDate DATETIME2(0)" +
    ",ProductionTime NVARCHAR(5)" +
    ",DayName NVARCHAR(9)" +
    ",Perfect_Trace BIT" +
    ",NWFGenerated_Trace BIT" +
    ",Altered_Trace BIT")]
public static IEnumerable Read_Trace([SqlFacet(MaxSize = 255)] SqlChars customer, [SqlFacet(MaxSize = 255)] SqlChars trace)
{
    var trc = trace.ToString();
    RegexList regexList = GetRegexList(customer.ToString(), trace.ToString());

    return new List<TraceContents>
    {
        new TraceContents
        {
            Site = GetValue(regexList.Site_Regex, trc),
            Line = SqlInt16.Parse(new string(GetValue(regexList.Line_Regex, trc).Where(char.IsNumber).ToArray())),
            Shift = GetValue(regexList.Shift_Regex, trc),
            ProductionDate = JulianToDate(GetValue(regexList.Date_Regex, trc)),
            ProductionTime = GetValue(regexList.Time_Regex, trc),
            DayName = GetValue(regexList.Day_Regex, trc),
            Perfect_Trace = regexList.Perfect_Trace,
            NWFGenerated_Trace = regexList.NWFGenerated_Trace,
            Altered_Trace = regexList.Altered_Trace
        }
    };
}

    public static void Read_Trace_Fill(
    Object traceObj,
    out string Site,
    out byte Line,
    out string Shift,
    out DateTime? ProductionDate,
    out string ProductionTime,
    out string DayName,
    out bool Perfect_Trace,
    out bool NWFGenerated_Trace,
    out bool Altered_Trace
)
    {
        var trace = (TraceContents)traceObj;
        Site = trace.Site;
        Line = trace.Line;
        Shift = trace.Shift;
        ProductionDate = trace.ProductionDate;
        ProductionTime = trace.ProductionTime;
        DayName = trace.DayName;
        Perfect_Trace = trace.Perfect_Trace;
        NWFGenerated_Trace = trace.NWFGenerated_Trace;
        Altered_Trace = trace.Altered_Trace;
    }

If the results are large (ie multiple rows) then you end up storing the whole thing in memory. Ideally you should use yield return to create a streaming IEnumerable. In this case you appear to be returning just a single row.