SQL Server: из представления, вызов функции с возвращающим табличное значение параметром

В SQL Server, если у меня есть функция со скалярным значением, которая принимает аргумент параметра с табличным значением, определенный следующим образом (упрощенный пример):

CREATE TYPE IntTableType AS TABLE(Value INT); 

CREATE FUNCTION dbo.MeetsCustomRequirements
(
    @TypeIDs IntTableType READONLY
)
RETURNS TINYINT AS 
BEGIN
    -- (Function logic here; returns either 0 or 1 based on the specified @TypeIDs values)
END

Я хотел бы иметь возможность вызывать эту функцию из представления, где возвращающий табличное значение параметр представления создается из встроенного запроса, например:

CREATE VIEW dbo.vEligibleItems AS 
    SELECT i.ItemID
    FROM tItems i
    WHERE dbo.MeetsCustomRequirements (
        (SELECT TypeID FROM tItemTypes WHERE ItemID = i.ItemID)
    );

Однако это не работает, потому что результат этого вложенного запроса SELECT не является IntTableType (хотя он является списком INT значений). (Определенная ошибка Error 206: Operand type clash: int is incompatible with IntTableType.)

Мой вопрос, состоящий из двух частей: есть ли способ вызвать функцию, принимающую аргумент параметра с табличным значением из представления? Если нет, то какие существуют альтернативные подходы для достижения того же результата?

База данных - это SQL Server 2008.

(Внесение логики в функцию, встроенную в представление, не идеально, потому что функция вызывается из нескольких разных мест, а не только из этого одного представления.)


sql sql-server table-valued-parameters
person Jon Schneider    schedule 26.08.2015    source источник
comment
Возможно, вам понадобится CROSS APPLY   -  person Lukasz Szozda    schedule 26.08.2015
comment
Можете ли вы превратить эту скалярную функцию во встроенную функцию с табличным значением? Обратите внимание, что это означает один оператор выбора. Если у вас несколько инструкций, это не поможет, на самом деле производительность может быть даже хуже. Тогда перекрестное применение - лучший подход. Присмотритесь, есть там сказуемое где твое. У вас есть скалярная функция, но вы пытаетесь передать ей весь столбец, и вам также понадобятся некоторые условия.   -  person Sean Lange    schedule 26.08.2015
comment
Да, кажется, лучше сделать внутреннее соединение между tItems и tItemTypes напрямую. Какая логика в dbo.MeetsCustomRequirements?   -  person ps2goat    schedule 26.08.2015
comment
В моем реальном коде функция скалярного значения дополнительно принимает несколько других параметров. Логика, по сути, представляет собой большой оператор CASE с несколькими предложениями WHEN, которые смотрят на значения входных параметров и возвращают 1 для различных комбинаций и возвращают 0, если ни одна из комбинаций не сработала.   -  person Jon Schneider    schedule 26.08.2015
comment
CASE-блок проверяет только одну строку или все строки из табличного параметра?   -  person CPMunich    schedule 26.08.2015
comment
@CPMunich Проверяет все строки.   -  person Jon Schneider    schedule 26.08.2015

Ответы (1)


arrow_upward
2
arrow_downward

Я не думаю, что это возможно с помощью VIEW

СОЗДАТЬ ВИД [schema_name. ] имя_представления [(столбец [, ... n])]

[С [, ... n]]

AS select_statement

[С ОПЦИЕЙ ПРОВЕРКИ]

[ ; ]

Максимальное значение синтаксиса представления - CTE. Чтобы передать TVP-аргумент, его нужно где-то объявить, но внутри определения вида это сделать нельзя.

Очень уродливое решение, просто концепция, а не фактический код (с использованием XML для передачи нескольких значений вспомогательной функции):

CREATE VIEW dbo.vEligibleItems
AS
WITH cte (view.*, XML_with_all_types_id) AS
(
    SELECT *,
       [XML_with_all_types_id] = magic_here(
            SELECT iTypeID FROM tItemTypes t WHERE WHERE t.ItemID = i.ItemID)
    FROM tItems i      
)
SELECT *
FROM cte
WHERE dbo.MeetsCustomRequirements_helper(XML_with_all_types_id) = 1

CREATE FUNCTION dbo.MeetsCustomRequirements_helper(@xml XML)
RETURNS INT
AS
(
   // unwrap xml to TVP
   DECLARE @tvp your_tvp_type;
   INSERT INTO @tvp(cols)
   SELECT *
   FROM @xml.node(...);

   DECLARE @result = dbo.MeetsCustomRequirements(@tvp);

   RETURN @result
)

Можете ли вы просто изменить свой вид на пользовательскую функцию с табличным значением и при необходимости прикрыть ее представлением, например (я знаю, что производительность будет плохой):

CREATE VIEW name
AS
SELECT *
FROM dbo.fn_getdata();

CREATE FUNCTION dbo.fn_getdata()
RETURNS @result TABLE
AS 
BEGIN

  /* multi-statement you can declare your TVP */

END
person Lukasz Szozda    schedule 26.08.2015