ptg
924
CHAPTER 29 Creating and Managing User-Defined Functions
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 popu-
lates 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
Download from www.wowebook.com
ptg
925
Creating and Managing User-Defined Functions
having avg(isnull(price, 0)) > @price
return
end
Notice the main differences between this version and the inline version: in the multistate-
ment 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 state-
ments 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.
Creating and Managing User-Defined Functions
In the preceding sections of this chapter, you saw some examples of creating functions.
The following sections discuss in more detail the CREATE FUNCTION syntax and the types of
operations allowed in functions. These sections also show how to create and manage T-
SQL functions by using SQL Server Management Studio (SSMS).
Creating User-Defined Functions
You create T-SQL functions by using T-SQL statements. You can enter the T-SQL code in
sqlcmd, SSMS, or any other third-party query tool that allows you to enter ad hoc T-SQL
code. The following sections first show the basic syntax for creating functions and then
show how you can create functions by using the features of SSMS.
Creating T-SQL Functions
User-defined functions can accept 0–2,100 input parameters but can return only a single
result: either a single scalar value or table result set.
The T-SQL syntax for the CREATE FUNCTION command for scalar functions is as follows:
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [AS] [ schema_name.]scalar_datatype [ = default ] }
29
Download from www.wowebook.com
ptg
926
CHAPTER 29 Creating and Managing User-Defined Functions
[ , n ] ] )
RETURNS scalar_datatype
[ WITH { [ ENCRYPTION ]
[ , SCHEMABINDING ]
[ , RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
[ , EXECUTE_AS_Clause ]

} ]
[ AS ]
BEGIN
SQL_Statements
RETURN scalar_expression
END
The syntax for the CREATE FUNCTION command for inline table-valued functions is as
follows:
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [AS] [ schema_name.]scalar_datatype [ = default ] }
[ , n ] ] )
RETURNS TABLE
[ WITH { [ ENCRYPTION ]
[ , SCHEMABINDING ]
[ , RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
[ , EXECUTE_AS_Clause ]
} ]
[ AS ]
RETURN [ ( ] select-stmt [ ) ]
The syntax for the CREATE FUNCTION command for multistatement table-valued functions
is as follows:
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [AS] [ schema_name.]scalar_datatype [ = default ] }
[ , n ] ] )
RETURNS @table_variable TABLE ( { column_definition | table_constraint }
[ , n ] )
[ WITH { [ ENCRYPTION ]
[ , SCHEMABINDING ]
[ , RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
[ , EXECUTE_AS_Clause ]

} ]
[ AS ]
BEGIN
SQL_Statements
RETURN
END
Download from www.wowebook.com
ptg
927
Creating and Managing User-Defined Functions
The types of SQL statements allowed in a function include the following:
. DECLARE statements to define variables and cursors that are local to the function.
. Assignments of values to variables that are local to the function, using the SET
command or an assignment select.
. Cursor operations on local cursors that are declared, opened, closed, and de-allocated
within the function. FETCH statements must assign values to local variables by using
the INTO clause.
. Control-of-flow statements such as IF, ELSE, WHILE, GOTO, and so on, excluding the
TRY CATCH statements.
. UPDATE, INSERT, and DELETE statements that modify table variables defined within
the function.
. EXECUTE statements that call an extended stored procedure. (Any results returned by
the extended stored procedure are discarded.)
. Other user-defined functions, up to a maximum nesting level of 32.
If you specify the ENCRYPTION option, the SQL statements used to define the function are
stored encrypted in the syscomments table. This prevents anyone from viewing the func-
tion source code in the database.
NOTE
If you choose to encrypt the function code, you should be sure to save a copy of the
script used to create the function to a file outside the database, in case you ever need

to modify the function or re-create it. After the source code for the function is encrypt-
ed, you cannot extract the original unencrypted source code from the database.
If a function is created with the SCHEMABINDING option, the database objects that the func-
tion references cannot be altered or dropped unless the function is dropped first or the
schema binding of the function is removed, using the ALTER FUNCTION command and
without specifying the SCHEMABINDING option. A CREATE FUNCTION statement with the
SCHEMABINDING option specified fails unless all the following conditions are met:
. Any user-defined functions and views referenced within the function are also
schema bound.
. Any objects referenced by the function are referenced using a two-part name
(schema.object_name).
. The function and the objects it references belong to the same database.
. The user executing the CREATE FUNCTION statement has REFERENCES permission on all
database objects that the function references.
29
Download from www.wowebook.com
ptg
928
CHAPTER 29 Creating and Managing User-Defined Functions
You can specify the SCHEMABINDING option only for T-SQL functions. The following example
modifies the AveragePricebyType2 function by specifying the SCHEMABINDING option:
ALTER FUNCTION AveragePricebyType2 (@price money = 0.0)
RETURNS @table table (type varchar(12) null, avg_price money null)
with schemabinding
AS
begin
insert @table
SELECT type, avg(price) as avg_price
FROM dbo.titles
group by type

having avg(price) > @price
return
end
The following example shows what happens if you try to modify a column in the titles
table referenced by the function:
alter table titles alter column price smallmoney null
go
Msg 5074, Level 16, State 1, Line 1
The object ‘AveragePricebyType2’ is dependent on column ‘price’.
Msg 5074, Level 16, State 1, Line 1
The statistics ‘price’ is dependent on column ‘price’.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN price failed because one or more objects access this
column.
If the RETURNS NULL ON NULL INPUT option is specified, the function automatically returns
NULL as a result, without invoking the function body. If this option is not specified, the
default option of CALLED ON NULL INPUT is applied. The following example shows the
difference between these two options:
CREATE FUNCTION striptime (@datetimeval datetime)
RETURNS datetime
AS
BEGIN
DECLARE @dateval datetime
SELECT @dateval = convert(date, isnull(@datetimeval, getdate()))
RETURN @dateval
END
GO
Download from www.wowebook.com
ptg
929

Creating and Managing User-Defined Functions
CREATE FUNCTION striptime2(@datetimeval datetime)
RETURNS datetime
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
DECLARE @dateval datetime
SELECT @dateval = convert(date, isnull(@datetimeval, getdate()))
RETURN @dateval
END
GO
select dbo.striptime(NULL), dbo.striptime2(NULL)

2006-06-05 00:00:00.000 NULL
The EXECUTE AS clause allows you to specify the security context under which the user-
defined function will execute. This way, you can control which user account SQL Server
uses to validate permissions on any database objects referenced by the function. This
option cannot be specified for inline table-valued functions.
Another key restriction on user-defined functions is that SQL statements within a func-
tion cannot generate side effects; that is, a user-defined function cannot generate perma-
nent changes to any resource whose scope extends beyond the function. For example, a
function cannot modify data in a table, operate on cursors not local to the function,
create or drop database objects, issue transaction control statements, or generate a result
set other than the defined function result via a SELECT statement or an extended stored
procedure that would be returned to the user. The only changes that can be made by the
SQL statements in a function are to the objects local to the function, such as local cursors
or variables.
A new feature in SQL Server 2008 is that you can now include most built-in system func-
tions within a user-defined function, even ones that are nondeterministic (that is, func-
tions that can return different data values on each call). For example, the getdate()

function is considered nondeterministic because even though it is always invoked with the
same argument, it returns a different value each time it is executed. However, the follow-
ing nondeterministic built-in functions are still not allowed in user-defined functions:
. newid()
. newsequentialid()
. rand()
. textptr()
User-defined functions can also call other user-defined functions, with a limit of 32 levels
of nesting. Nesting of functions can help improve the modularity and reusability of
29
Download from www.wowebook.com
ptg
930
CHAPTER 29 Creating and Managing User-Defined Functions
FIGURE 29.1 Creating a new function from the Object Browser in SSMS.
function code. For example, the following version of the getonlydate() function uses the
striptime() function example shown earlier in this chapter:
CREATE FUNCTION dbo.getonlydate()
RETURNS datetime
as
BEGIN
DECLARE @date datetime
SET @date = dbo.striptime( getdate())
RETURN @date
end
Using SSMS to Create Functions
To create a function by using SSMS, open the Object Explorer to the database in which
you want to create the function. Then select the Programmability node, right-click the
Functions node, select New, and then choose one of the three available options as shown
in Figure 29.1:

. Inline Table-Valued Function
. Multistatement Table-Valued Function
. Scalar-Valued Function
Download from www.wowebook.com
ptg
931
Creating and Managing User-Defined Functions
29
SSMS opens a new query window populated with a template for that type of function.
Listing 29.1 shows an example of the default template code for an inline table-valued
function that would be opened into a new query window.
LISTING 29.1 An Example of a New Function Creation Script Generated by SSMS
================================================
Template generated from Template Explorer using:
Create Inline Function (New Menu).SQL

Use the Specify Values for Template Parameters
command (Ctrl-Shift-M) to fill in the parameter
values below.

This block of comments will not be included in
the definition of the function.
================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
=============================================
Author: <Author,,Name>
Create date: <Create Date,,>

Description: <Description,,>
=============================================
CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName>
(
Add the parameters for the function here
<@param1, sysname, @p1> <Data_Type_For_Param1, , int>,
<@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE
AS
RETURN
(
Add the SELECT statement with parameter references here
SELECT 0
)
GO
You can modify the template code as necessary to name the function and to specify the
parameters, return value, and function body. When you are finished, you execute the
Download from www.wowebook.com
ptg
932
FIGURE 29.2 Using the Specify Values for Template Parameters dialog with functions in
SSMS.
contents of the query window to create the function. After you create a function success-
fully, you should save the source code to a file by choosing File, Save or File, Save As. This
way, you can re-create the function from the file if it is accidentally dropped from the
database.
One thing you might notice about the function templates is that they contain template
parameters for parameter names and function names, for example. These template para-
meters are in the format <parameter_name, data_type, value>:

. parameter_name is the name of the template parameter in the script.
. data_type is the optional data type of the template parameter.
. value is the default value to be used to replace every occurrence of the template
parameter in the script.
You can automatically substitute values for template parameters by selecting Query,
Specify Values for Template Parameters or by pressing Ctrl+Shift+M. The Specify Values for
Template Parameters dialog, shown in Figure 29.2, appears.
CHAPTER 29 Creating and Managing User-Defined Functions
Enter the values for the template parameters in the Value column and then click OK.
SSMS then substitutes any values you specified wherever the template parameter is defined
within the template.
An alternative way to create a function from a template is to use the Template Explorer in
SSMS. You can open the Template Explorer by selecting View, Template Explorer in SSMS
(see Figure 29.3) or by pressing Ctrl+Alt+T. The Template Explorer window appears in
SSMS (which is also shown in Figure 29.3).
Download from www.wowebook.com
ptg
933
FIGURE 29.3 Opening the Template Explorer to create functions in SSMS.
Creating and Managing User-Defined Functions
29
You can double-click the template for the type of function you want to create or right-
click the desired template and then select Open. SSMS opens a new query window popu-
lated with the template code.
NOTE
You are also able to edit the provided function temp lates availab le in the Template
Explorer by right-clicking them and selecting Edit. You can then customize the tem-
plates to include code fragments, comments, or a structure that is more to your prefer-
ences and save the changes to the template file. However, it is generally
recommended that you not modify the provided templates alone and instead create

your own custom templates.
Creating Custom Function Templates
To create a custom function template, right-click the Function folder in the Template
Explorer and select New. SSMS then creates an entry in the Template Explorer, and you can
specify the name for the template, as shown in Figure 29.4.
To begin adding code to the template, you double-click it or right-click and select Open. A
blank query window appears, and you can use it to enter the new template code. Probably
the best way to get started is to copy the template code from one of the templates
provided with SQL Server 2008.
Download from www.wowebook.com