Inline User-Defined Functions

Inline user-defined functions are a subset of user-defined functions that return a table. Inline functions can be used to achieve the functionality of parameterized views.

Consider this view:

CREATE VIEW vw_CustomerNamesInWA AS
SELECT CustomerID, CompanyName
FROM Northwind.dbo.Customers
WHERE Region = 'WA'

You can create a more generalized version, vw_CustomerNamesInRegion, by replacing the WHERE Region = 'WA' with a WHERE Region = @RegionParameter and letting 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. This is an example of a function that allows users to specify the region in their select:

CREATE FUNCTION fn_CustomerNamesInRegion
                 ( @RegionParameter nvarchar(30) )
RETURNS table
AS
RETURN (
        SELECT CustomerID, CompanyName
        FROM Northwind.dbo.Customers
        WHERE Region = @RegionParameter
       )
GO
-- Example of calling the function for a specific region
SELECT *
FROM fn_CustomerNamesInRegion(N'WA')
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.

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 view, the function can apply the user's parameterized filters to pull specific rows from the result set that was built by the CREATE INDEX statement. The complex aggregations and joins are done once, at CREATE INDEX time, and all subsequent queries referencing the inline function filter rows from the simplified, stored result set. For example:

1. 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.
   
   
2. You create a clustered index on vw_QuarterlySales to materialize a result set containing the summarized data.
   
   
3. You create an inline function to filter the summarized data:

   CREATE FUNCTION fn_QuarterlySalesByStore
        (
         @StoreID int
        )
   RETURNS table
   AS
   RETURN (
           SELECT *
           FROM SalesDB.dbo.vw_QuarterlySales
           WHERE StoreID = @StoreID
          )
   

4. 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.