SQL Server 2008 R2 : Types of User-Defined Functions

1/29/2013 4:16:05 PM

SQL Server supports three types of user-defined functions:

The next few sections take an in-depth look at the differences between the function types and how and where you can use them.

Scalar Functions

A scalar function is like the standard built-in functions provided with SQL Server. It returns a single scalar value that can be used anywhere a constant expression can be used in a query. (You saw an example of this in the earlier description of the getonlydate() function.)

A scalar function typically takes one or more arguments and returns a value of a specified data type. Every T-SQL function must return a result using the RETURN statement. The value to be returned can be contained in a local variable defined within the function, or the value can be computed in the RETURN statement. The following two functions are variations of a function that returns the average price for a specified type of book from the titles table:

use bigpubs2008
CREATE FUNCTION AverageBookPrice(@booktype varchar(12) = '%')
    DECLARE @avg money
    SELECT @avg = avg(price)
    FROM titles
    WHERE type like @booktype

    RETURN @avg
CREATE FUNCTION AverageBookPrice2(@booktype varchar(12) = '%')
    RETURN ( SELECT avg(price)
             FROM titles
             WHERE type like @booktype)

A scalar function can be used anywhere a constant expression can be used. For example, SQL Server doesn’t allow aggregate functions in a WHERE clause unless they are contained in a subquery. The AvgBookPrice() function lets you compare against the average price without having to use a subquery:

select title_id, type, price from titles
where price > dbo.AverageBookPrice('popular_comp')

title_id type         price
-------- ------------ ---------------------
PC1035   popular_comp 17.1675
PS2091   psychology   17.0884

When invoking a user-defined scalar function, you must include the schema name. If you omit the schema name, you get the following error, even if the function is created in your default schema or exists only in the dbo schema in the database:

select AverageBookPrice('popular_comp')

Server: Msg 195, Level 15, State 10, Line 1
'AverageBookPrice' is not a recognized function name.

You can return the value from a user-defined scalar function into a local variable in two ways. You can assign the result to a local variable by using the SET statement or an assignment select, or you can use the EXEC statement. The following commands are functionally equivalent:

declare @avg1 money,
        @avg2 money,
        @avg3 money
select @avg1 = dbo.AverageBookPrice('popular_comp')
set @avg2 = dbo.AverageBookPrice('popular_comp')
exec @avg3 = dbo.AverageBookPrice 'popular_comp'
select @avg1 as avg1, @avg2 as avg2, @avg3 as avg3
Warning: Null value is eliminated by an aggregate or other SET operation.
avg1                  avg2                  avg3
--------------------- --------------------- ---------------------
16.0643               16.0643               16.0643

Notice, however, that when you use a function in an EXEC statement, you invoke it similarly to the way you invoke a stored procedure, and you do not use parentheses around the function parameters. Also, when you invoke a function in the EXEC statement, the function generates the following warning message: “Warning: Null value is eliminated by an aggregate or other SET operation.” This warning isn’t generated when the function is invoked in the SET or SELECT statement. To avoid confusion, you should stick to using the EXEC statement for stored procedures and invoke scalar functions as you would normally invoke a SQL Server built-in function.

Table-Valued Functions

A table-valued user-defined function returns a rowset instead of a single scalar value. You can invoke a table-valued function in the FROM clause of a SELECT statement, just as you would a table or view. In some situations, a table-valued function can almost be thought of as a view that accepts parameters, so the result set is determined dynamically. A table-valued function specifies the keyword TABLE in its RETURNS clause.

Table-valued functions are of two types: inline and multistatement. The two types of table-valued functions return the same thing, and they are also invoked the same way. The only real difference between them is the way the function is written to return the rowset. The next couple sections look at each of these types of table-valued functions.

Inline Table-Valued Functions

An inline table-valued function specifies only the TABLE keyword in the RETURNS clause, without table definition information. The code inside the function is a single RETURN statement that invokes a SELECT statement. For example, you could create an inline table-valued function that returns a rowset of all book types and the average price for each type, where the average price exceeds the value passed into the function:

use bigpubs2008
CREATE FUNCTION AveragePricebyType (@price money = 0.0)

    RETURN ( SELECT type, avg(isnull(price, 0)) as avg_price
             FROM titles
             group by type
             having avg(isnull(price, 0)) > @price)

You can invoke the function by referencing it in a FROM clause as you would a table or view:

select * from AveragePricebyType (15.00)

type         avg_price
------------ ---------------------
business     15.0988
mod_cook     15.4236

Notice that when you invoke a table-valued function, you do not have to specify the schema name as you do with a user-defined scalar function.

Multistatement Table-Valued Functions

Multistatement table-valued functions differ from inline functions in two major ways:

  • The RETURNS clause specifies a table variable and its definition.

  • The body of the function contains multiple statements, at least one of which populates the table variable with data values.

You define a table variable in the RETURNS clause by using the TABLE data type. The syntax to define the table variable is similar to the CREATE TABLE syntax. Note that the name of the table variable comes before the TABLE keyword:

RETURNS @variable TABLE ( column definition | table_constraint [, ...] )

The scope of the table variable is limited to the function in which it is defined. Although the contents of the table variable are returned as the function result, the table variable itself cannot be accessed or referenced outside the function.

Within the function in which a table variable is defined, that table variable can be treated like a regular table. You can perform any SELECT, INSERT, UPDATE, or DELETE statement on the rows in a table variable, except for SELECT INTO. Here’s an example:

INSERT INTO @table SELECT au_lname, au_fname from authors

The following example defines the inline table-valued function AveragePricebyType() as a multistatement table-valued function called AveragePricebyType2():

use bigpubs2008
CREATE FUNCTION AveragePricebyType2 (@price money = 0.0)
RETURNS @table table (type varchar(12) null, avg_price money null)
    insert @table
       SELECT type, avg(isnull(price,0)) as avg_price
             FROM titles
             group by type
             having avg(isnull(price, 0)) > @price

Notice the main differences between this version and the inline version: in the multistatement version, you have to define the structure of the table rowset you are returning and also have to include the BEGIN and END statements as wrappers around the multiple statements that the function can contain. Other than that, both functions are invoked the same way and return the same rowset:

select * from AveragePricebyType2 (15.00)

type         avg_price
------------ ---------------------
business     15.0988
mod_cook     15.4236

Why use multistatement table-valued functions instead of inline table-valued functions? Generally, you use multistatement table-valued functions when you need to perform further operations (for example, inserts, updates, or deletes) on the contents of the table variable before returning a result set. You would also use them if you need to perform more complex logic or additional processing on the input parameters of the function before invoking the query to populate the table variable.

Most View
- Diagnosing SQL Server 2012 Using Extended Events : Viewing Data Captured by Extended Events (part 3) - Querying a Ring Buffer Using T-SQL
- SQL Server 2012 : Using XML Data - XQuery and FLWOR Operations
- Windows Server 2012 : A complete virtualization platform (part 6) - Live Migration without shared storage, Performing Live Migration
- Microsoft PowerPoint 2010 : Coordinating Multiple Animations (part 2) - Set Time Between Animations,Modify an Animation
- Application Lifecycle Management in SharePoint 2013 : Planning your Key Development Phases and Release Model (part 2) - Release Models
- Sharepoint 2013 : Welcome to the Central Administration Web Site (part 3) - Backup and Restore, Security
- SQL Server 2012 : Normal Forms (part 3) - Third Normal Form, The Boyce-Codd Normal Form
- Windows 8 : Disks and Storage Devices - Disk Cleanup and Check Disk
- Sharepoint 2013 : Customizing a SharePoint Site - Create a Content Type
- Windows Server 2012 : Deploying Dynamic Access Control (part 1) - Preparing Claims
Top 10
- Sharepoint 2013 : Developing Integrated Apps for Office and Sharepoint Solutions - The New App Model for Office
- Overview of Oauth in Sharepoint 2013 : Application Authorization - On-Premises App Authentication with S2S
- Overview of Oauth in Sharepoint 2013 : Application Authorization - Requesting Permissions Dynamically
- Microsoft Excel 2010 : Working with Graphics - Inserting a Diagram,Inserting an Object
- Microsoft Excel 2010 : Working with Graphics - Inserting WordArt, Using Smart Art in Excel
- Microsoft Excel 2010 : Working with Graphics - Using AutoShapes
- Overview of Oauth in Sharepoint 2013 : Application Authentication (part 2) - Managing Tokens in Your Application
- Overview of Oauth in Sharepoint 2013 : Application Authentication (part 1) - Using TokenHelper
- Overview of Oauth in Sharepoint 2013 : Creating and Managing Application Identities
- Overview of Oauth in Sharepoint 2013 : Introduction to OAuth