Табличная функция CLR с аргументом-массивом

У меня есть функция SQL CLR, подобная этой:

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;
    }

}

Я хотел бы использовать его следующим образом:

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)
)

У меня проблема в том, что arrayOfInt несовместим с sql_variant. Можно ли написать табличную функцию CLR с аргументом массива (таблицы)?


sql-server user-defined-functions table-valued-parameters sqlclr
person Václav Dajbych    schedule 20.01.2011    source источник

Ответы (1)


arrow_upward
11
arrow_downward

SQLCLR не поддерживает табличные параметры (TVP):

  • СОЗДАТЬ ПРОЦЕДУРУ

    [ type_schema_name. ] data_type
    ...
    Рекомендации по процедурам CLR:

    • Table-valued or cursor data types cannot be used as parameters.
  • #P4# <блочная цитата> #P5#

Однако у вас есть несколько вариантов:

  1. Если массив представляет собой простой список чисел и/или строк, вы всегда можете отправить список значений с разделителями (через SqlString/NVARCHAR(MAX)) и использовать String.Split() для его распаковки.

  2. Если массив более сложный (т. е. несколько полей), вы можете обернуть данные в XML и передать их как SqlXml.

  3. Или, если у вас есть сложный массив, вы можете создать CLR UDT, который будет любой структурой, которую вы хотите, и передать ее в функцию. Однако это требует немного больше усилий.

  4. Кроме того, имейте в виду, что табличные параметры — это просто таблицы (табличные переменные), а не структуры данных в памяти, такие как массивы и коллекции. Основное преимущество и вариант использования TVP заключается в снижении сложности и повышении производительности при отправке данных в SQL Server из приложения. Если вы уже находитесь внутри SQL Server и создаете табличную переменную, а затем хотите передать ее хранимой процедуре CLR (или, возможно, функции), то все, что вам нужно сделать, это посмотреть на проблему немного по-другому, и вы может выполнить одно и то же:

    • Use a temporary table instead of a table variable
    • Передайте имя временной таблицы хранимой процедуре CLR
    • Используйте внутрипроцессное соединение (т. е. строка соединения = "Context Connection = true;"), поскольку оно может обращаться к локальным временным объектам.
    • Вы можете использовать временную таблицу в любом SQL, который вы выполняете, используя имя таблицы, которое было передано в
    • Вы можете получить данные из этой таблицы в контексте .NET, выполнив простой SELECT * для имени таблицы, которое было передано, а затем прочитать каждую строку через SqlCommand.ExecuteReader() и SqlDataReader.Read()
person Solomon Rutzky    schedule 22.01.2011
comment
это забавно: вы всегда можете отправить список значений с разделителями, потому что я оказался здесь, когда искал способ передать таблицу строк, чтобы я мог использовать CLR для объединения их в одну строку с разделителями :) - person Ryan; 10.07.2015