4

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.
Joshua Taylor
  • 84,998
  • 9
  • 154
  • 353
dopoto
  • 1,124
  • 1
  • 10
  • 20

1 Answers1

2

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

Sire
  • 4,086
  • 4
  • 39
  • 74
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • That is of absolutely no use in describing how to fix the problem. – Random Jun 12 '18 at 03:05
  • @Random - the OP obviously disagreed with your assessment since they accepted the answer. They really were just at the point where they didn't understand that they needed to schemabind their function. I pointed them to the documentation for functions and told them to use schemabinding. – Damien_The_Unbeliever Jun 12 '18 at 05:24