Inline User-Defined Functions
Inline user-defined functions are a subset of user-defined functions that return a table data type. Inline functions can be used to achieve the functionality of parameterized views.
The following example returns store names and cities for a specified region.
USE AdventureWorks2008R2; GO CREATE VIEW CustomersByRegion AS SELECT DISTINCT S.Name AS Store, A.City FROM Sales.Store AS S JOIN Sales.BusinessEntityAddress AS BEA ON BEA.BusinessEntityID = S.BusinessEntityID JOIN Person.Address AS A ON A.AddressID = BEA.AddressID JOIN Person.StateProvince SP ON SP.StateProvinceID = A.StateProvinceID WHERE SP.Name = N'Washington'; GO
This view would be better if it were more generalized and let users specify the region they are interested in viewing. Views, however, do not support parameters in the search conditions specified in the WHERE clause. Inline user-defined functions can be used to support parameters in the search conditions specified in the WHERE clause. The following example creates an inline function that allows users to specify the region in their query:
USE AdventureWorks2008R2; GO IF OBJECT_ID(N'Sales.ufn_CustomerNamesInRegion', N'IF') IS NOT NULL DROP FUNCTION Sales.ufn_CustomerNamesInRegion; GO CREATE FUNCTION Sales.ufn_CustomerNamesInRegion ( @Region nvarchar(50) ) RETURNS table AS RETURN ( SELECT DISTINCT s.Name AS Store, a.City FROM Sales.Store AS s INNER JOIN Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID = s.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = bea.AddressID INNER JOIN Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID WHERE sp.Name = @Region ); GO -- Example of calling the function for a specific region SELECT * FROM Sales.ufn_CustomerNamesInRegion(N'Washington') ORDER BY City; GO
Inline user-defined functions follow these rules:
The RETURNS clause contains only the keyword table. You do not have to define the format of a return variable, because it is set by the format of the result set of the SELECT statement in the RETURN clause.
There is no function_body delimited by BEGIN and END.
The RETURN clause contains a single SELECT statement in parentheses. The result set of the SELECT statement forms the table returned by the function. The SELECT statement used in an inline function is subject to the same restrictions as SELECT statements used in views.
The table-valued function accepts only constants or @local_variable arguments
Inline functions can also be used to increase the power of indexed views. The indexed view itself cannot use parameters in its WHERE clause search conditions to tailor the stored result set to specific users. You can, however, define an indexed view that stores the complete set of data that matches the view, and then define an inline function over the indexed view that contains parameterized search conditions that allow users to tailor their results. If the view definition is complex, most of the work performed to build a result set involves operations such as building aggregates or joining several tables when the clustered index is created on the view. If you then create an inline function that references the indexed view, the function can apply the user's parameterized filters to return specific rows from the materialized result set of the indexed view. For example:
You define a view vw_QuarterlySales that aggregates all sales data into a result set that reports summarized sales data by quarter for all stores.
You create a clustered index on vw_QuarterlySales to materialize a result set containing the summarized data.
You create an inline function to filter the summarized data:
CREATE FUNCTION dbo.ufn_QuarterlySalesByStore ( @StoreID int ) RETURNS table AS RETURN ( SELECT * FROM SalesDB.dbo.vw_QuarterlySales WHERE StoreID = @StoreID )
Users can then get the data for their specific store by selecting from the inline function:
SELECT * FROM fn_QuarterlySalesByStore(14432)
Most of the work needed to satisfy the queries issued at Step 4 is to aggregate the sales data by quarter. This work is done once at Step 2. Each individual SELECT statement in Step 4 uses the function fn_QuarterlySalesByStore to filter out the aggregated data specific to their store.