CREATE FUNCTION

Creates a user-defined function, which is a saved Transact-SQL routine that returns a value. User-defined functions cannot be used to perform a set of actions that modify the global database state. User-defined functions, like system functions, can be invoked from a query. They also can be executed through an EXECUTE statement like stored procedures.

User-defined functions are modified using ALTER FUNCTION, and dropped using DROP FUNCTION.

Syntax

Scalar Functions

CREATE  FUNCTION [ owner_name. ] function_name
    ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

RETURNS scalar_return_data_type

[ WITH < function_option> [ [,] ...n] ]

[ AS ]

BEGIN
    function_body
    RETURN scalar_expression
END

Inline Table-valued Functions

CREATE FUNCTION [ owner_name. ] function_name
    ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

RETURNS TABLE

[ WITH < function_option > [ [,] ...n ] ]

[ AS ]

RETURN [ ( ] select-stmt [ ) ]

Multi-statement Table-valued Functions

CREATE FUNCTION [ owner_name. ] function_name
    ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

RETURNS @return_variable TABLE < table_type_definition >

[ WITH < function_option > [ [,] ...n ] ]

[ AS ]

BEGIN
    function_body
    RETURN
END

< function_option > ::=
    { ENCRYPTION | SCHEMABINDING }

< table_type_definition > :: =
    ( { column_definition | table_constraint } [ ,...n ] )

Arguments

owner_name

Is the name of the user ID that owns the user-defined function. owner_name must be an existing user ID.

function_name

Is the name of the user-defined function. Function names must conform to the rules for identifiers and must be unique within the database and to its owner.

@parameter_name

Is a parameter in the user-defined function. One or more parameters can be declared in a CREATE FUNCTION statement. A function can have a maximum of 1,024 parameters. The value of each declared parameter must be supplied by the user when the function is executed, unless a default for the parameter is defined. When a parameter of the function has a default value, the keyword "default" must be specified when calling the function in order to get the default value. This behavior is different from parameters with default values in stored procedures in which omitting the parameter also implies the default value.

Specify a parameter name using an at sign (@) as the first character. The parameter name must conform to the rules for identifiers. Parameters are local to the function; the same parameter names can be used in other functions. Parameters can take the place only of constants; they cannot be used in place of table names, column names, or the names of other database objects.

scalar_parameter_data_type

Is the parameter data type. All scalar data types, including bigint and sql_variant, can be used as a parameter for user-defined functions. The timestamp data type and user-defined data types not supported. Nonscalar types such as cursor and table cannot be specified.

scalar_return_data_type

Is the return value of a scalar user-defined function. scalar_return_data_type can be any of the scalar data types supported by SQL Server, except text, ntext, image, and timestamp.

scalar_expression

Specifies the scalar value that the scalar function returns.

TABLE

Specifies that the return value of the table-valued function is a table.

In inline table-valued functions, the TABLE return value is defined through a single SELECT statement. Inline functions do not have associated return variables.

In multi-statement table-valued functions, @return_variable is a TABLE variable, used to store and accumulate the rows that should be returned as the value of the function.

function_body

Specifies that a series of Transact-SQL statements, which together do not produce a side effect, define the value of the function. function_body is used only in scalar functions and multi-statement table-valued functions.

In scalar functions, function_body is a series of Transact-SQL statements that together evaluate to a scalar value.

In multi-statement table-valued functions, function_body is a series of Transact-SQL statements that populate a table return variable.

select-stmt

Is the single SELECT statement that defines the return value of an inline table-valued function.

ENCRYPTION

Indicates that SQL Server encrypts the system table columns containing the text of the CREATE FUNCTION statement. Using ENCRYPTION prevents the function from being published as part of SQL Server replication.

SCHEMABINDING

Specifies that the function is bound to the database objects that it references. If a function is created with the SCHEMABINDING option, then the database objects that the function references cannot be altered (using the ALTER statement) or dropped (using a DROP statement).

The binding of the function to the objects it references is removed only when one of two actions take place:

• The function is dropped.
  
  
• The function is altered (using the ALTER statement) with the SCHEMABINDING option not specified.

A function can be schema-bound only if the following conditions are true:

• The user-defined functions and views referenced by the function are also schema-bound.
  
  
• The objects referenced by the function are not referenced using a two-part name.
  
  
• The function and the objects it references belong to the same database.
  
  
• The user who executed the CREATE FUNCTION statement has REFERENCES permission on all the database objects that the function references.

The CREATE FUNCTION statement with the SCHEMABINDING option specified will fail if the above conditions are not true.

Remarks

User-defined functions are either scalar-valued or table-valued. Functions are scalar-valued if the RETURNS clause specified one of the scalar data types. Scalar-valued functions can be defined using multiple Transact-SQL statements.

Functions are table-valued if the RETURNS clause specified TABLE. Depending on how the body of the function is defined, table-valued functions can be classified as inline or multi-statement functions.

If the RETURNS clause specifies TABLE with no accompanying column list, the function is an inline function. Inline functions are table-valued functions defined with a single SELECT statement making up the body of the function. The columns, including the data types, of the table returned by the function are derived from the SELECT list of the SELECT statement defining the function.

If the RETURNS clause specifies a TABLE type with columns and their data types, the function is a multi-statement table-valued function.

The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function:

• Assignment statements.
  
  
• Control-of-Flow statements.
  
  
• DECLARE statements defining data variables and cursors that are local to the function.
  
  
• SELECT statements containing select lists with expressions that assign values to variables that are local to the function.
  
  
• Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.
  
  
• INSERT, UPDATE, and DELETE statements modifying table variables local to the function.
  
  
• EXECUTE statements calling an extended stored procedures.

Function Determinism and Side Effects

Functions are either deterministic or nondeterministic. They are deterministic when they always return the same result any time they are called with a specific set of input values. They are nondeterministic when they could return different result values each time they are called with the same specific set of input values.

Nondeterministic functions can cause side effects. Side effects are changes to some global state of the database, such as an update to a database table, or to some external resource, such as a file or the network (for example, modify a file or send an e-mail message).

Built-in nondeterministic functions are not allowed in the body of user-defined functions; they are as follows:

Although nondeterministic functions are not allowed in the body of user-defined functions, these user-defined functions still can cause side effects if they call extended stored procedures.

Functions that call extended stored procedures are considered nondeterministic because extended stored procedures can cause side effects on the database. When user defined functions call extended stored procedures that can have side effects on the database, do not rely on a consistent result set or execution of the function.

Calling extended stored procedures from functions

The extended stored procedure, when called from inside a function, cannot return result sets to the client. Any ODS APIs that return result sets to the client will return FAIL.  The extended stored procedure could connect back to Microsoft® SQL Server™; however, it should not attempt to join the same transaction as the function that invoked the extended stored procedure.

Similar to invocations from a batch or stored procedure, the extended stored procedure will be executed in the context of the Windows® security account under which SQL Server is running. The owner of the stored procedure should consider this when giving EXECUTE privileges on it to users.

Function Invocation

Scalar-valued functions may be invoked where scalar expressions are used, including computed columns and CHECK constraint definitions. When invoking scalar-valued functions, at minimum use the two-part name of the function.

[database_name.]owner_name.function_name ([argument_expr][,...])

If a user-defined function is used to define a computed column, the function's deterministic quality also defines whether an index may be created on that computed column. An index can be created on a computed column that uses a function only if the function is deterministic. A function is deterministic if it always returns the same value, given the same input.

Table-valued functions can be invoked using a single part name.

[database_name.][owner_name.]function_name ([argument_expr][,...])

System table functions that are included in Microsoft® SQL Server™ 2000 need to be invoked using a '::' prefix before the function name.

SELECT *
FROM ::fn_helpcollations()

Transact-SQL errors that cause a statement to be stopped and then continued with the next statement in a stored procedure are treated differently inside a function. In functions, such errors will cause the function execution to be stopped. This in turn will cause the statement that invoked the function to be stopped.

Permissions

Users should have the CREATE FUNCTION permission to execute the CREATE FUNCTION statement.

CREATE FUNCTION permissions default to members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles. Members of sysadmin and db_owner can grant CREATE FUNCTION permissions to other logins by using the GRANT statement.

Owners of functions have EXECUTE permission on their functions. Other users do not have EXECUTE permissions unless EXECUTE permissions on the specific function are granted to them.

In order to create or alter tables with references to user-defined functions in the CONSTRAINT, DEFAULT clauses, or computed column definition, the user must also have REFERENCES permission to the functions.

Examples

A. Scalar-valued user-defined function that calculates the ISO week

In this example, a user-defined function, ISOweek, takes a date argument and calculates the ISO week number.  For this function to calculate properly, SET DATEFIRST 1 must be invoked before the function is called.

CREATE FUNCTION ISOweek  (@DATE datetime)
RETURNS int
AS
BEGIN
   DECLARE @ISOweek int
   SET @ISOweek= DATEPART(wk,@DATE)+1
      -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
   IF (@ISOweek=0) 
      SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 
         AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
   IF ((DATEPART(mm,@DATE)=12) AND 
      ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
      SET @ISOweek=1
   RETURN(@ISOweek)
END

Here is the function call. Notice that DATEFIRST is set to 1.

SET DATEFIRST 1
SELECT master.dbo.ISOweek('12/26/1999') AS 'ISO Week'

Here is the result set.

ISO Week
----------------
52

B. Inline table-valued function

This example returns an inline table-valued function.

USE pubs
GO
CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
      FROM sales s, titles t
      WHERE s.stor_id = @storeid and
      t.title_id = s.title_id)

C. Multi-statement table-valued function

Given a table that represents a hierarchical relationship:

CREATE TABLE employees (empid nchar(5) PRIMARY KEY, 
      empname nvarchar(50), 
      mgrid nchar(5) REFERENCES employees(empid), 
      title nvarchar(30)
      )

The table-valued function fn_FindReports(InEmpID), which -- given an Employee ID -- returns a table corresponding to all the employees that report to the given employee directly or indirectly. This logic is not expressible in a single query and is a good candidate for implementing as a user-defined function.

CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
RETURNS @retFindReports TABLE (empid nchar(5) primary key,
   empname nvarchar(50) NOT NULL,
   mgrid nchar(5),
   title nvarchar(30))
/*Returns a result set that lists all the employees who report to given 
employee directly or indirectly.*/
AS
BEGIN
   DECLARE @RowsAdded int
   -- table variable to hold accumulated results
   DECLARE @reports TABLE (empid nchar(5) primary key, 
      empname nvarchar(50) NOT NULL,
      mgrid nchar(5),
      title nvarchar(30),
      processed tinyint default 0)
-- initialize @Reports with direct reports of the given employee 
   INSERT @reports
   SELECT empid, empname, mgrid, title, 0
   FROM employees 
   WHERE empid = @InEmpId 
   SET @RowsAdded = @@rowcount
   -- While new employees were added in the previous iteration
   WHILE @RowsAdded > 0
   BEGIN
      /*Mark all employee records whose direct reports are going to be 
   found in this iteration with processed=1.*/
      UPDATE @reports
      SET processed = 1
      WHERE processed = 0
      -- Insert employees who report to employees marked 1.
      INSERT @reports
      SELECT e.empid, e.empname, e.mgrid, e.title, 0
      FROM employees e, @reports r
      WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1
      SET @RowsAdded = @@rowcount
      /*Mark all employee records whose direct reports have been found
   in this iteration.*/
      UPDATE @reports
      SET processed = 2
      WHERE processed = 1
   END
   
   -- copy to the result of the function the required columns
   INSERT @retFindReports
   SELECT empid, empname, mgrid, title 
   FROM @reports
   RETURN
END
GO

-- Example invocation
SELECT * 
FROM fn_FindReports('11234')
GO

See Also

ALTER FUNCTION

DROP FUNCTION

Invoking User-defined Functions

User-defined Functions