Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

CLR Table-valued function with array argument

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?

like image 999
Václav Dajbych Avatar asked Jan 20 '11 15:01

Václav Dajbych


People also ask

Can we use CTE in table valued function?

In addition, it is also perfectly valid to use Common Table Expression (CTE) in ITVF. ); Note that ITVF cannot have BEGIN and END block encapsulating the RETURN statement. You will get error “Incorrect syntax near BEGIN” when use BEGIN and END statement as follow.

What is inline table valued function in SQL Server?

Taking the definition of what a user defined function (UDF) is from Books Online, an inline table-valued function (iTVF) is a table expression that can accept parameters, perform an action and provide as its return value, a table.

What are multi statement table valued functions in SQL Server?

The multistatement table-valued, user-defined function combines the scalar function's capability to contain complex code with the inline table-valued function's capability to return a result set. This type of function creates a table variable and then populates it within code.


1 Answers

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()
like image 61
Solomon Rutzky Avatar answered Oct 14 '22 06:10

Solomon Rutzky