462
Part IV: Programming with T-SQL
To create a function with schema binding, add the option after RETURNS and before AS
during function creation, as shown here:CREATE FUNCTION FunctionName (Input Parameters)
RETURNS DataType
WITH SCHEMA BINDING
AS
BEGIN;
Code;
RETURNS Expression;
END;Schema binding not only alerts the developer that the change may affect an object, it also
prevents the change. To remove schema binding so that changes can be made, ALTER the
function so that schema binding is no longer included.Multistatement Table-Valued Functions
The multistatement table-valued, user-defi ned function combines the scalar function’s
capability to contain complex code with the inline table-valued function’s capability to
return a result set. This type of function creates a table variable and then populates it
within code. The table is then passed back from the function so that it may be used within
SELECT statements. From a query optimizer standpoint, it is treated much like an external
call, like joining a stored procedure’s result set to a table using OPENQUERY.The primary benefi t of the multistatement table-valued, user-defi ned function is that com-
plex result sets may be generated within code and then easily used with a SELECT state-
ment. This enables you to build complex logic into a query and solve problems that would
otherwise be diffi cult to solve without a cursor.The APPLY command may be used with multistatement table-valued, user-defi ned func-
tions in the same way that it’s used with inline user-defi ned functions.Creating a Multistatement Table-Valued Function
The syntax to create the multistatement table-valued function is similar to that of the sca-
lar user-defi ned function:CREATE FUNCTION FunctionName (InputParamenters)
RETURNS @TableName TABLE (Columns)
AS
BEGIN;
Code to populate table variable
RETURN;
END;c18.indd 462c18.indd 462 7/30/2012 5:41:47 PM7/30/2012 5:41:47 PM
http://www.it-ebooks.info