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 AdventureWorks; GO CREATE VIEW CustomersByRegion AS SELECT DISTINCT S.Name AS Store, A.City FROM Sales.Store AS S JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID JOIN Person.Address AS A ON A.AddressID = CA.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 AdventureWorks; 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 JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID JOIN Person.Address AS A ON A.AddressID = CA.AddressID JOIN Person.StateProvince 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'); 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
QuarterlySalesthat aggregates all sales data into a result set that reports summarized sales data by quarter for all stores.
You create a clustered index on
QuarterlySalesto 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.