CLR table function with array argument

Question

I have a SQL CLR function like this:

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 as follows:

 DECLARE @x arrayOfInt INSERT INTO @x VALUES (10) INSERT INTO @x VALUES (20) INSERT INTO @x VALUES (30) SELECT * FROM dbo.MyClrFunction(@x) 

arrayOfInt:

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

The problem is that arrayOfInt is not compatible with sql_variant. Is it possible to write a CLR Table-value function that takes an array (table) argument?

Answer 1

SQLCLR does not support table parameters (TVP):

• CREATE PROCEDURE

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

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

• CREATE FUNCTION

  [type_schema_name. ] parameter_data_type
  ... For CLR functions, all data types are allowed, including custom CLR types, except for text , ntext , image specific table types, and timestamp data types.

However, you have several options:

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

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

• Or, if you have a complex array, you can create a CLR UDT like any structure you like and pass it to a function. It takes a little more effort.

• Also, keep in mind that Table-Valued Parameters are just those tables (table variables), not memory structures like arrays and collections. The main advantage and use case of TVP is to reduce complexity and increase productivity when sending data to SQL Server from an application. If you are already in SQL Server, so you create a table variable and then want to pass it to the CLR stored procedure (or maybe a function), then all you need to do is look at the problem a little differently, and you can do the same basic thing:

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