Function in Where clause inside Inline Table-Valued Function

Question

If I do this, it is not good correct?

ALTER FUNCTION [dbo].[fn_ItemInventory] (@CustomerId)
RETURNS TABLE AS
   SELECT * FROM Inventory WHERE Warehouse = fn_GetWarehouse(@CustomerId)

Ideally I want to do this, but this is not allowed for ITVF:

ALTER FUNCTION [dbo].[fn_ItemInventory] (@CustomerId)
RETURNS TABLE AS
   SET @Warehouse = fn_GetWarehouse(@CustomerId)
   SELECT * FROM Inventory WHERE Warehouse = @Warehouse

I don't want to use MSTVF because it cannot be optimized by the query optimizer. In the end I use this work-around:

ALTER FUNCTION [dbo].[fn_ItemInventory] (@CustomerId)
RETURNS TABLE AS
   SELECT * FROM fn_Inventory(fn_GetWarehouse(@CustomerId))

The fn_Inventory accepts @Warehouse as parameter and queries Inventory table using the parameter, thus avoid the issue. Is there no standard pattern for handling this situation?

Why you don't like the first version? How do you have defined the fn_Inventory? — Hamlet Hakobyan
– Hamlet Hakobyan, Commented May 22, 2014 at 18:55
Try ... WHERE Warehouse = dbo.fn_GetWarehouse(@CustomerId) — Bogdan Sahlean
– Bogdan Sahlean, Commented May 22, 2014 at 19:01
Sorry I was unclear. "Function inside Where clause" can be a performance issue, as the function may be evaluated once for each result row — Sarsaparilla
– Sarsaparilla, Commented May 23, 2014 at 14:52

Jaaz Cole · Accepted Answer · 2014-05-22 20:41:01Z

1

You forgot your return statement.

ALTER FUNCTION [dbo].[fn_ItemInventory] (@CustomerId)
RETURNS TABLE AS
   RETURN SELECT * FROM Inventory WHERE Warehouse = fn_GetWarehouse(@CustomerId)

Alternately, you need begin and end to mark blocks of code for anything more than a single return statement, so:

ALTER FUNCTION [dbo].[fn_ItemInventory] (@CustomerId)
RETURNS @tableName TABLE (<TableDefinition>) AS
BEGIN
   SET @Warehouse = fn_GetWarehouse(@CustomerId)
   INSERT INTO @TableName SELECT * FROM Inventory WHERE Warehouse = @Warehouse
   RETURN
END

answered May 22, 2014 at 20:41

Jaaz Cole

3,1981 gold badge17 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

liebs19 · Accepted Answer · 2014-05-23 13:12:55Z

0

I would try to make the fn_GetWarehouse and inline table valued function as well and join it on.

ALTER FUNCTION [dbo].[fn_ItemInventory] (@CustomerId)
RETURNS TABLE AS
RETURN (
    SELECT 
         _Inventory.*
    FROM Inventory _Inventory
    join fn_GetWarehouse(@CustomerId) _Warehouse
        on _Warehouse.warehouse = _Inventory.Warehouse
)

edited May 23, 2014 at 13:12

answered May 23, 2014 at 12:57

liebs19

5493 silver badges14 bronze badges

Collectives™ on Stack Overflow

Function in Where clause inside Inline Table-Valued Function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related