CLR SplitString Exception while executing in SQL Server

Question

Hi I'm new in C# programming and just copied and pasted the CLR function from @Aaronaught's answer to use it in a SQL Server 2012:

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

public class CustomFunctions
{
    [
        Microsoft.SqlServer.Server.SqlFunction
        (
            FillRowMethodName = "FillRow",
            TableDefinition = "nRow int, string nvarchar(4000)"
        )
    ]
    public static IEnumerable SplitString(SqlString str, SqlString delimiter)
    {
        if (str.IsNull || delimiter.IsNull)
        {
            return null;
        }

        string[] values = str.Value.Split(delimiter.Value.ToCharArray());

        StringPair[] results = new StringPair[values.Length];

        for (int i = 0; i < values.Length; i++)
        {
            results[i] = new StringPair(i + 1, values[i]);
        }
        return results;
    }

    public static void FillRow(object row, ref int id, ref string value)
    {
        StringPair pair = (StringPair)row;
        id = pair.ID;
        value = pair.Value;
    }

    public class StringPair
    {
        public StringPair(int id, string value)
        {
            this.ID = id;
            this.Value = value;
        }

        public int ID { get; private set; }
        public string Value { get; private set; }
    }
};

I compile as assembly:

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'clr enabled', 1
RECONFIGURE
GO
sp_configure 'show advanced options', 0
RECONFIGURE
GO

CREATE ASSEMBLY [CustomFunctionsC]
FROM 'C:\custom\CustomFunctions\CustomFunctionsC.dll'
WITH PERMISSION_SET = SAFE
GO

IF OBJECT_ID('[dbo].[SplitString]') IS NOT NULL
DROP FUNCTION [dbo].[SplitString]
GO
CREATE FUNCTION [dbo].[SplitString](@string_in [nvarchar](4000), @delimiter [nvarchar](4000))
RETURNS  TABLE (
    nRow    INT,
    string  NVARCHAR(4000) NULL
) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [CustomFunctionsC].[CustomFunctions].[SplitString]
GO

And then I execute it in SQL Server:

DECLARE @string VARCHAR(256) = '1,2,3,4,5,6,7';

SELECT * FROM [dbo].[SplitString](@string, ',')

It throws an exception System.ArgumentNullException:

System.ArgumentNullException: Value cannot be null. Parameter name: ptr System.ArgumentNullException: at System.Runtime.InteropServices.Marshal.PtrToStringUni(IntPtr ptr, Int32 len)

Since I'm not able to debug the CLR function in the current environment I don't even know which line can be throwing that exception.

Answer 1

maybe not the answer you are looking for but Why not make a native sql function to do this? It's going to be cheaper and It will perform better, here is one way :

CREATE or alter FUNCTION STRING_SPLIT2
(
  @List      nvarchar(max),
  @Delimiter nchar(1)
)
RETURNS @t table (Item nvarchar(max))
AS
BEGIN
  SET @List += @Delimiter;
  ;WITH a(f,t) AS  
  (
    SELECT CAST(1 AS bigint), CHARINDEX(@Delimiter, @List)
    UNION ALL
    SELECT t + 1, CHARINDEX(@Delimiter, @List, t + 1) 
    FROM a WHERE CHARINDEX(@Delimiter, @List, t + 1) > 0
  )  
  INSERT @t SELECT SUBSTRING(@List, f, t - f) FROM a OPTION (MAXRECURSION 0);
  RETURN;  
END
GO  

If you are on SQL Server 2017 or above , SQL server has a built-in function called 'STRING_SPLIT` that does the same thing :

DECLARE @string VARCHAR(256) = '1,2,3,4,5,6,7';

SELECT * FROM string_split(@string, ',')