SQL Server: From a view, calling a function with a table-valued parameter

Question

In SQL Server, if I have a scalar-value function that takes a table-valued parameter argument, defined as follows (simplified example):

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

I'd like to be able to call that function from a view, where the table-valued parameter of the view is generated from an inline query, like:

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

However, this doesn't work because the result of that nested SELECT query isn't an IntTableType (even though it is a list of INT values). (Specific error generated is Error 206: Operand type clash: int is incompatible with IntTableType.)

My two-part question: Is there a way to call a function taking a table-valued parameter argument from a view? If not, what alternative approaches are available to accomplish the same thing?

Database is SQL Server 2008.

(Bringing the logic in the function inline in the view is not ideal because the function is called from several different places, not just from this one view.)

Answer 1

I don't think it is possible with VIEW

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]

[ WITH [ ,...n ] ]

AS select_statement

[ WITH CHECK OPTION ]

[ ; ]

With view syntax maximum you can get is CTE. To pass TVP-argument you need to declare it somewhere, but you can't do it inside view definition.

Very ugly solution just concept not actual code (using XML to pass multiple values to helper function):

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
)

Can you just change your View to Table-Valued User-Defined Function and cover its with view if needed like (I know performance will be poor):

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