CLR Table-valued function with array argument

Question

I have a SQL CLR function like this one:

public partial class UserDefinedFunctions {
    [Microsoft.SqlServer.Server.SqlFunction(TableDefinition = "number int", FillRowMethodName = "FillRow")]
    public static IEnumerable MyClrFunction(object obj) {
        // read obj array input and then
        var result = new ArrayList();
        result.Add((SqlInt32)1);
        result.Add((SqlInt32)2);
        result.Add((SqlInt32)3);
        return result;
    }

    public static void FillRow(object obj, out SqlInt32 number) {
        number = (SqlInt32)obj;
    }

}

I would like to use it this way:

DECLARE @x arrayOfInt

INSERT INTO @x VALUES (10)
INSERT INTO @x VALUES (20)
INSERT INTO @x VALUES (30)

SELECT * FROM dbo.MyClrFunction(@x)

arrayOfInt is:

CREATE TYPE [dbo].[arrayOfInt] AS TABLE(
[item] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
    (
    [item] ASC
    ) WITH (IGNORE_DUP_KEY = OFF)
)

Problem I have is that arrayOfInt is incompatible with sql_variant. Is possible to write CLR Table-valued function which has an array (table) argument?

Answer 1

SQLCLR does not support Table-Valued Parameters (TVPs):

• CREATE PROCEDURE

  [ type_schema_name. ] data_type
  ...
  Guidelines for CLR procedures:

  • Table-valued or cursor data types cannot be used as parameters.

• CREATE FUNCTION

  [ type_schema_name. ] parameter_data_type
  ... For CLR functions, all data types, including CLR user-defined types, are allowed except text, ntext, image, user-defined table types and timestamp data types.

However, you do have several options:

1. If the array is a simple list of numbers and/or strings you can always send in a delimited list of values (via SqlString / NVARCHAR(MAX)) and use String.Split() to unpack it.

2. If the array is more complex (i.e. multiple fields), then you can wrap the data in XML and pass that in as SqlXml.

3. Or, if you have a complex array then you can create a CLR UDT to be whatever structure you like and pass that into the Function. This requires a bit more effort, though.

4. Also, keep in mind that Table-Valued Parameters are just that, tables (table variables), and not in-memory data structures such as Arrays and Collections. The main benefit and use-case of TVPs are in reducing complexity and increasing performance when sending data to SQL Server from an application. If you are already inside SQL Server such that you are creating a table variable and then wanting to pass it to a CLR stored procedure (or maybe function), then all you need to do is look at the problem in a slightly different way and you can accomplish the same basic thing:

   • Use a temporary table instead of a table variable
   • Pass the temporary table name to the CLR stored procedure
   • Use the in-process connection (i.e. connection string = "Context Connection = true;") because it can access local temporary objects
   • You can use the temporary table in any SQL that you execute by using the table name that was passed in
   • You can get the data from that table into the .NET context by doing a simple SELECT * on the table name that was passed in, and then read each row via SqlCommand.ExecuteReader() and SqlDataReader.Read()