User-Defined Functions are created with the CREATE FUNCTION statement, which has the following syntax:
CREATE FUNCTION [schema_name.]function_name
[({@param } type [= default]) {,...}
RETURNS {scalar_type | [@variable] TABLE}
[WITH {ENCRYPTION | SCHEMABINDING}
[AS] {block | RETURN (select_statement)}
Code language: JavaScript (javascript)
schema_name is the name of the schema to which the ownership of the created UDF is assigned. function_name is the name of the new function. @param is an input parameter, while type specifies its data type. Parameters are values passed from the caller of the UDF and are used within the function. default specifies the optional default value of the corresponding parameter. (Default can also be NULL.)
The RETURNS clause defines a data type of the value returned by the UDF. This data type can be any of the standard data types supported by the database system, including the TABLE data type. (The only standard data type that you cannot use is TIMESTAMP.)
UDFs are either scalar-valued or table-valued. A scalar-valued function returns an atomic (scalar) value. This means that in the RETURNS clause of a scalar-valued function, you specify one of the standard data types. Functions are table-valued if the RETURNS clause returns a set of rows (see the next subsection).
The WITH ENCRYPTION option encrypts the information in the system catalog that contains the text of the CREATE FUNCTION statement. In that case, you cannot view the text used to create the function. (Use this option to enhance the security of your database system.)
The alternative clause, WITH SCHEMABINDING, binds the UDF to the database objects that it references. Any attempt to modify the structure of the database object that the function references fails. (The binding of the function to the database objects it references is removed only when the function is altered, so the SCHEMABINDING option is no longer specified.)
Database objects that are referenced by a function must fulfill the following conditions if you want to use the SCHEMABINDING clause during the creation of that function:
- All views and UDFs referenced by the function must be schema-bound.
- All database objects (tables, views, or UDFs) must be in the same database as the function.
block is the BEGIN/END block that contains the implementation of the function. The final statement of the block must be a RETURN statement with an argument. (The value of the argument is the value returned by the function.) In the body of a BEGIN/END block, only the following statements are allowed:
- Assignment statements such as SET
- Control-of-flow statements such as WHILE and IF
- DECLARE statements defining local data variables
- SELECT statements containing SELECT lists with expressions that assign to variables that are local to the function
- INSERT, UPDATE, and DELETE statements modifying variables of the TABLE data type that are local to the function
By default, only the members of the sysadmin fixed server role and the db_owner and db_ddladmin fixed database roles can use the CREATE FUNCTION statement. However, the members of these roles may assign this privilege to other users by using the GRANT CREATE FUNCTION statement.
Example 8.18 shows the creation of the function called compute_costs.
-- This function computes additional total costs that arise
-- if budgets of projects increase
USE sample;
GO
CREATE FUNCTION compute_costs (@percent INT =10)
RETURNS DECIMAL(16,2)
BEGIN
DECLARE @additional_costs DEC (14,2), @sum_budget dec(16,2)
SELECT @sum_budget = SUM (budget) FROM project
SET @additional_costs = @sum_budget * @percent/100
RETURN @additional_costs
END
Code language: PHP (php)
The function compute_costs computes additional costs that arise when all budgets of projects increase. The single input variable, @percent, specifies the percentage of increase of budgets. The BEGIN/END block first declares two local variables: @additional_costs and @sum_budget. The function then assigns to @sum_budget the sum of all budgets, using the SELECT statement. After that, the function computes total additional costs and returns this value using the RETURN statement.
Invoking User-Defined Functions
Each UDF can be invoked in Transact-SQL statements, such as SELECT, INSERT, UPDATE, or DELETE. To invoke a function, specify the name of it, followed by parentheses. Within the parentheses, you can specify one or more arguments. Arguments are values or expressions that are passed to the input parameters that are defined immediately after the function name. When you invoke a function, and all parameters have no default values, you must supply argument values for all of the parameters and you must specify the argument values in the same sequence in which the parameters are defined in the CREATE FUNCTION statement.
Example 8.19 shows the use of the compute_costs function (Example 8.18) in a SELECT statement.
USE sample;
SELECT project_no, project_name
FROM project
WHERE budget < dbo.compute_costs(25)
Code language: PHP (php)
The result is

The SELECT statement in Example 8.19 displays names and numbers of all projects where the budget is lower than the total additional costs of all projects for a given percentage.
Note – Each function used in a Transact-SQL statement must be specified using its two-part name—that is, schema_name.function_name.
Table-Valued Functions
As you already know, functions are table-valued if the RETURNS clause returns a set of rows. Depending on how the body of the function is defined, table-valued functions can be classified as inline or multistatement functions. If the RETURNS clause specifies TABLE with no accompanying list of columns, the function is an inline function. Inline functions return the result set of a SELECT statement as a variable of the TABLE data type (see Example 8.20). A multistatement table-valued function includes a name followed by TABLE. (The name defines an internal variable of the type TABLE.) You can use this variable to insert rows into it and then return the variable as the return value of the function.
Example 8.20 shows a function that returns a variable of the TABLE data type.
USE sample;
GO
CREATE FUNCTION employees_in_project (@pr_number CHAR(4))
RETURNS TABLE
AS RETURN (SELECT emp_fname, emp_lname
FROM works_on, employee
WHERE employee.emp_no = works_on.emp_no
AND project_no = @pr_number)
Code language: PHP (php)
The employees_in_project function is used to display names of all employees that belong to a particular project. The input parameter @pr_number specifies a project number. While the function generally returns a set of rows, the RETURNS clause contains the TABLE data type. (Note that the BEGIN/END block in Example 8.20 must be omitted, while the RETURN clause contains a SELECT statement.)
Example 8.21 shows the use of the employees_in_project function.
USE sample;
SELECT *
FROM employees_in_project('p3')
Code language: PHP (php)
The result is

Table-Valued Functions and APPLY
The APPLY operator is a relational operator that allows you to invoke a table-valued function for each row of a table expression. This operator is specified in the FROM clause of the corresponding SELECT statement in the same way as the JOIN operator is applied. There are two forms of the APPLY operator:
- CROSS APPLY
- OUTER APPLY
The CROSS APPLY operator returns those rows from the inner (left) table expression that match rows in the outer (right) table expression. Therefore, the CROSS APPLY operator is logically the same as the INNER JOIN operator.
The OUTER APPLY operator returns all the rows from the inner (left) table expression. (For the rows for which there are no corresponding matches in the outer table expression, it contains NULL values in columns of the outer table expression.) OUTER APPLY is logically equivalent to LEFT OUTER JOIN.
Examples 8.22 and 8.23 show how you can use APPLY.
-- generate function
create function dbo.fn_getjob(@empid AS INT)
RETURNS TABLE AS
RETURN
SELECT job
FROM works_on
WHERE emp_no = @empid
AND job IS NOT NULL AND project_no = 'p1';
Code language: CSS (css)
The fn_getjob() function in Example 8.22 returns the set of rows from the works_on table. This result set is “joined” in Example 8.23 with the content of the employee table.
-- use CROSS APPLY
SELECT E.emp_no, emp_fname, emp_lname, job
FROM employee as E
CROSS APPLY dbo.fn_getjob(E.emp_no) AS A
-- use OUTER APPLY
SELECT E.emp_no, emp_fname, emp_lname, job
FROM employee as E
OUTER APPLY dbo.fn_getjob(E.emp_no) AS A
Code language: CSS (css)
The result is

In the first query of Example 8.23, the result set of the table-valued function fn_getjob() is “joined” with the content of the employee table using the CROSS APPLY operator. fn_getjob() acts as the right input, and the employee table acts as the left input. The right input is evaluated for each row from the left input, and the rows produced are combined for the final output.
The second query is similar to the first one, but uses OUTER APPLY, which corresponds to the outer join operation of two tables.
Table-Valued Parameters
In all versions previous to SQL Server 2008, it was difficult to send many parameters to a routine. In that case you had to use a temporary table, insert the values into it, and then call the routine. Since SQL Server 2008, you can use table-valued parameters to simplify this task. These parameters are used to deliver a result set to the corresponding routine.
Example 8.24 shows the use of a table-valued parameter.

Example 8.24 first defines the type called departmentType as a table. This means that its type is the TABLE data type, so rows can be inserted in it. In the insertProc procedure, the @Dallas variable, which is of the departmentType type, is specified. (The READONLY clause specifies that the content of the table variable cannot be modified.) In the subsequent batch, data is added to the table variable, and after that the procedure is executed. The procedure, when executed, inserts rows from the table variable into the temporary table #dallasTable. The content of the temporary table is as follows:
The use of table-valued parameters gives you the following benefits:
- It simplifies the programming model in relation to routines.
- It reduces the round trips to the server.
- The resulting table can have different numbers of rows.