Creating a CLR Table Value

Question

I am trying to create a simple CLR function with a table evaluation that takes a comma-separated string as a parameter, splits it, and returns it as multiple lines

After a few online tutorials, I ended up:

[SqlFunction(FillRowMethodName = "FillRow",TableDefinition="val nvarchar(1000)")]
public static IEnumerable SqlFunction1(SqlString val)
{
    string[] splitStr = val.Value.Split(',');
    return splitStr;
}
private static void FillRow(Object obj, out SqlString str)
{
    object[] row = (object[])obj;
    str = (string)row[0];
}

However, performing it with

select * from dbo.SqlFunction1('1,2,3,4,5')

Returns the following error

Msg 6260, Level 16, State 1, Line 1
An error occurred while getting new row from user defined Table Valued Function : 
System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Object[]'.
System.InvalidCastException: 
   at UserDefinedFunctions.FillRow(Object obj, SqlString& str)
.

Answer 1

You take a link to a string and try to pass it to an array of objects, which raises an InvalidCastException.

Your method SqlFunction1returns an array of strings, so the method FillRowwill be called with reference to the string. Return the link to the object back to the string and then create a value from it SqlString:

private static void FillRow(Object obj, out SqlString str) {
  string row = (string)obj;
  str = new SqlString(row);
}

Answer 2

#, SQL, . . , . - , .

using System;
using System.Data;
using System.Collections;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction(Name = "StringParserCLR",
    FillRowMethodName = "FillRow",
    TableDefinition = "string nvarchar(500)")]

    public static IEnumerable StringParserCLR(SqlString str, SqlChars delimiter)
    {
        if (delimiter.Length == 0)
        {
            return new string[1] { str.Value };
        }
        return str.Value.Split(delimiter[0]);
    }

    public static void FillRow(object row, out SqlString str)
    {
        str = new SqlString((string)row);
    }
};