Database
 

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
go
CREATE FUNCTION AverageBookPrice(@booktype varchar(12) = '%')
RETURNS money
AS
BEGIN
    DECLARE @avg money
    SELECT @avg = avg(price)
    FROM titles
    WHERE type like @booktype

    RETURN @avg
END
go
CREATE FUNCTION AverageBookPrice2(@booktype varchar(12) = '%')
RETURNS money
AS
BEGIN
    RETURN ( SELECT avg(price)
             FROM titles
             WHERE type like @booktype)
END

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')
go

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')
go

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
go
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
go
CREATE FUNCTION AveragePricebyType (@price money = 0.0)
RETURNS table
AS

    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)
go

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
go
CREATE FUNCTION AveragePricebyType2 (@price money = 0.0)
RETURNS @table table (type varchar(12) null, avg_price money null)
AS
begin
    insert @table
       SELECT type, avg(isnull(price,0)) as avg_price
             FROM titles
             group by type
             having avg(isnull(price, 0)) > @price
    return
end

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)
go

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.

 
Others
 
 
 
Most View
 
- Sharepoint 2010 : Windows PowerShell Remoting (part 2) - Entering a Remote Session, Running SharePoint 2010 Cmdlets Remotely
- Sharepoint 2013 : Community portals and sites - Moderating discussions - Mark a discussion as featured
- Windows Phone 8 : Working with People - The People Hub (part 1) - Viewing a Profile
- Application Lifecycle Management in SharePoint 2013 : Planning your Customization Model and Release Packaging Approach (part 1) - Customization Models
- Windows Phone 7 to Windows Phone 8 : App publication (part 1) - Preparing for publication
- Active Directory 2008 : Managing OUs (part 1) - Moving, Deleting, and Renaming OUs
- Microsoft Project 2010 : Strategic Importance of Project 2010
- SQL Server 2012 : Data Design Patterns (part 2) - Many-to-Many Pattern
- Taking Microsoft Project 2010 for a Test Drive (part 3) - Listing project tasks
- SQL Server 2012 : Backup and Recovery Planning - Recovery Models (part 1)
 
 
Top 10
 
- Microsoft PowerPoint 2010 : Working with Charts - Modifying and Formatting Charts (part 3) - Formatting Charts
- Microsoft PowerPoint 2010 : Working with Charts - Modifying and Formatting Charts (part 2) - Modifying Your Chart’s Layout
- Microsoft PowerPoint 2010 : Working with Charts - Modifying and Formatting Charts (part 1) - Modifying Chart Design
- Microsoft PowerPoint 2010 : Working with Charts - Inserting a Chart from Excel
- Microsoft PowerPoint 2010 : Working with Charts - Inserting Charts
- Microsoft PowerPoint 2010 : Working with Charts - Understanding Charts
- Sharepoint 2010 : Windows PowerShell Remoting (part 2) - Entering a Remote Session, Running SharePoint 2010 Cmdlets Remotely
- Sharepoint 2010 : Windows PowerShell Remoting (part 1)
- Sharepoint 2010 : Windows PowerShell Scripts (part 3) - Writing Comment-Based Help Topics in Scripts,Using Functions in Scripts , Customizing Windows PowerShell with Profile Scripts
- Sharepoint 2010 : Windows PowerShell Scripts (part 2) - Executing Scripts, Using Parameters in Scripts