Officially, SQL Server supports three types of user-defined functions:
Inline table-valued functions
Multistatement table-valued 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 are like the built-in functions provided with SQL Server. They return a single scalar value that can be used anywhere a constant expression can be used in a query, as you saw in the previous example of the getdateonly() function.
Scalar functions typically take one or more arguments and return a value of a specified datatype. All functions 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:
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
As mentioned earlier in this chapter, the scalar functions 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 22.9500 PS1372 psychology 21.5900
When invoking a user-defined scalar function, you must include the owner name. The owner name is typically the name of the user that created the function. If you omit the owner name, you will see the following error, even if the function is owned by dbo and is the only instance of such a function 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 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 --------------------- --------------------- --------------------- 21.4750 21.4750 21.4750
Notice, however, that when using a function in an EXEC statement, it is invoked similarly to a stored procedure and no parentheses are used around the function parameters. Also, when invoked in the EXEC statement, the function generated the warning message Warning: Null value is eliminated by an aggregate or other SET operation. This warning wasn't generated when invoked in the SET or SELECT statement. To avoid confusion, 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 user-defined functions return 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. What the two types return and the way they are invoked are the same. The only real difference is the way the function is written to return the rowset. The next couple of sections look at each of these types of 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:
CREATE FUNCTION AveragePricebyType (@price money = 0.0) RETURNS table AS RETURN ( SELECT type, avg(price) as avg_price FROM titles group by type having avg(price) > @price)
You can invoke the function by referencing it in a FROM clause as you would for a table or view:
select * from AveragePricebyType (15.00) go type avg_price ------------ --------------------- popular_comp 21.4750 trad_cook 15.9633
Notice that when you invoke a table-valued function, the owner name does not have to be specified as it does with a user-defined scalar function.
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.
Before getting into an example of a multistatement table-valued function, this chapter will briefly discuss table variables. (For more information on table variables, see Chapter 26.)
The table datatype is defined in the RETURNS clause. The syntax to define the table variable is similar to the CREATE TABLE syntax. Note that the name of the 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. While the contents of the table variable are returned as the function result, the table variable itself cannot be accessed or referenced outside the function itself.
Within the function, table variables can be treated like a regular table. Any SELECT, INSERT, UPDATE, or DELETE statement can be performed on the rows in a table variable, except for SELECT INTO and an INSERT where the result set to be inserted is from a stored procedure, as in the following example:
INSERT INTO table_variable EXEC stored_procedure
The following example defines the inline table-valued function AveragePricebyType() as a multistatement table-valued function:
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(price) as avg_price FROM titles group by type having avg(price) > @price return end
Notice the main differences between this version and the inline version. In the multistatement version, you had to define the structure of the table rowset you are returning and also had 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 ------------ --------------------- popular_comp 21.4750 trad_cook 15.9633
Why use multistatement table-valued functions instead of inline table-valued functions? The main reason is if you need to perform further inserts, updates, or deletes on the contents of the table variable before returning a result set, or if you need to perform additional processing on the input parameters to the function before invoking the query to populate the table variable.