Error when creating view - [dbo].[someFunction] is not schema bound

Question

I get this error when creating a view: "Cannot schema bind view 'dbo.viewStock'. 'dbo.GetStockCurrentQuantityByProduct' is not schema bound."

BACKGROUND: What I actually want to achieve is improve the speed of a query that retrieves Product Id / Current Stock. The current stock is calculated by a function that counts the units in/units out for a specific product ('dbo.GetStockCurrentQuantityByProduct'). I am exploring a possible solution - creating an indexed view to hold product Ids and current stocks, so I can select directly from it for faster query execution:

    CREATE VIEW [dbo].[viewStock] with schemabinding
    as 
    SELECT P.ProductId, 
    dbo.GetStockCurrentQuantityByProduct(P.ProductId) AS Quantity 
    FROM dbo.Product 

When I execute this, I get the error:

    Cannot schema bind view 'dbo.viewStock'. 
'dbo.GetStockCurrentQuantityByProduct' is not schema bound.

Answer 1

If you use schemabinding in a view or function, all called views or functions must also use use schemabinding.

Schemabinding makes it so you cannot accidentally change the underlying tables and columns if this would break the view. Which seems useful in your scenario.

The solution is therefore to modify GetStockCurrentQuantityByProduct so it also uses schemabinding.

See the SCHEMABINDING option of CREATE FUNCTION or ALTER FUNCTION