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

Can you change that scalar function into an inline table valued function? Please note, that means a single select statement. It is not going to help if you have multiple statements, in fact the performance might even be worse. Then a cross apply is a better approach. Look closely are your where predicate there. You have a scalar function but you are trying to pass it a whole column, and you would need some of condition there too. — Sean Lange
– Sean Lange, Commented Aug 26, 2015 at 15:21
Yes, this seems to be better to do an inner join between tItems and tItemTypes directly. What kind of logic is in dbo.MeetsCustomRequirements? — ps2goat
– ps2goat, Commented Aug 26, 2015 at 15:22
In my real code, the scalar-value function additionally takes several other parameters. The logic is essentially a large CASE statement with multiple WHEN clauses that look at the input param values and returns 1 for various combinations, and returns 0 if none of the combinations are hit. — Jon Schneider
– Jon Schneider, Commented Aug 26, 2015 at 15:28
does the CASE-block only checks one row or all rows from the table-valued-Parameter? — CPMunich
– CPMunich, Commented Aug 26, 2015 at 15:59

Lukasz Szozda · Accepted Answer · 2015-08-26 19:11:27Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related