Inline User-defined Functions

Artikkeli
12/03/2008

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 Function Rules

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 and Indexed Views

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.

Jaa

Inline User-defined Functions

Inline User-defined Function Rules

Inline Functions and Indexed Views

See Also

Concepts

Help and Information

Lisäresursseja