CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Sarsaparilla
Sarsaparilla

Reputation: 6670

Function in Where clause inside Inline Table-Valued Function

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?

Upvotes: 0

Views: 526

Answers (2)

liebs19
liebs19

Reputation: 549

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
)

Upvotes: 0

Jaaz Cole
Jaaz Cole

Reputation: 3180

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

Upvotes: 1

Related Questions