SQL Server 2000 Stored Procedures Handbook (Experts Voice)

There is an option available with UDFs, which is similar to that for views, for binding the columns used in any SELECT statement creating a TABLE data type to the underlying table. Schema binding will stop a table, a referenced column within a function, or a view from being dropped or altered from the underlying table and therefore keep our existing functions working.

Note that generally any good DBA will check existing dependencies by running sp_depends before dropping a column. However, by making our function schema-bound we can add in a layer of extra safety.

To make a function schema-bound we need to use the WITH SCHEMABINDING statement after the RETURNS statement. Apart from this, we have to meet a few other requirements:

• All objects must be from the same database

• All objects must be referenced by two part names (<ownerName>.<objectName>)

• User must have REFERENCE permissions to the tables, views, and user-defined functions used in code

• We cannot use the SELECT * syntax for returning values, even in an EXISTS expression

• All objects employed are schema-bound

If we look at our fn_CustUnShippedOrders function again, we can make this schema-bound by simply adding the WITH SCHEMABINDING statement, as we have already fulfilled all the other criteria:

ALTER FUNCTION fn_CustUnShippedOrders (@CustId NCHAR(5)) RETURNS @UnShipped TABLE (CustomerId NCHAR(5), OrderDate DATETIME NULL, RequiredDate DATETIME NULL, Shipper NVARCHAR(40), [Order Value] MONEY ) WITH SCHEMABINDING AS ... -- rest of the code is similar to the previous listing

Another important point concerning schema-bound functions is that the function itself would have to be deterministic to be able to apply schema binding.