CREATE function SQL Server user-defined function _mssql

Creates a user-defined function that 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 state of the global database. As with system functions, user-defined functions can be invoked from a query. It can also be executed as a stored procedure through an EXECUTE statement.
The user-defined function is modified with ALTER function and is dropped with the drop function.

Grammar
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 
] 

Multi-statement table-valued function

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 >:: = 

Parameters
Owner_name
The name of the user ID that owns the user-defined function. The owner_name must be an existing user ID.
Function_name
The name of the user-defined function. The function name must conform to the rules of the identifier, which must be unique to its owner in the database.
@parameter_name
The parameters of the user-defined function. You can declare one or more parameters in the CREATE FUNCTION statement. A function can have up to 1,024 parameters. The value of each declared parameter must be specified by the user when the function executes, unless the default value of the parameter is already defined. If the parameter of a function has a default value, the default keyword must be specified in order for the function to be invoked. This behavior differs from the parameter that has a default value in the stored procedure, and omitting the parameter in the stored procedure also means using the default value.
Use the @ symbol as the first character to specify the parameter name. Parameter names must conform to the rules for identifiers. The arguments for each function are used only for the function itself, and the same parameter names can be used in other functions. Parameters can only be substituted for constants, not for table names, column names, or other database object names.
Scalar_parameter_data_type
The data type of the parameter. All scalar data types, including bigint and sql_variant, can be used as parameters for user-defined functions. Timestamp data types and user-defined data types are not supported. Non-scalar 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 returned by a scalar function.
TABLE
Specifies that the return value of a table-valued function is a table.
In an inline table-valued function, the table return value is defined by a single SELECT statement. The inline function has no associated return variable.
In a multiple-statement table-valued function, @return_variable is a table variable that is used to store and accumulate rows that should be returned as function values.
Function_body
Specifies the values of a series of Transact-SQL statement-defined functions that together do not produce side effects. Function_body is used only for scalar functions and multiple statement table-valued functions.
In scalar functions, Function_body is a series of Transact-SQL statements that combine to obtain a scalar value.
In a multiple-statement table-valued function, Function_body is a series of Transact-SQL statements that populate a table return variable.
Select-stmt
is a single SELECT statement that defines the return value of an inline table-valued function.
Encryption
Indicates that SQL Server encrypts the system table columns that contain the text of the CREATE FUNCTION statement. Use encryption to avoid publishing functions as part of SQL Server replication.
Schemabinding
Specifies that the function is bound to the database object it references. If the function was created with the schemabinding option, you cannot change (using the ALTER statement) or drop (use the DROP statement) the database object referenced by the function.
The binding relationship of a function to its referenced object is only lifted if one of the following two situations occurs:
Removed the function.

The function was changed (using the ALTER statement) without specifying the SCHEMABINDING option.
A function can be bound to a schema only if the following conditions are true:
The user-defined functions and views referenced by the function are also bound to the schema.

The object referenced by the function is not referenced by a two-part name.

The function and its referenced objects belong to the same database.

The user executing the CREATE function statement has REFERENCES permissions on all database objects referenced by the function.
If you do not meet the above criteria, the CREATE FUNCTION statement that specifies the SCHEMABINDING option will fail.
Comments
A user-defined function is a scalar-valued function or a table-valued function. If the RETURNS clause specifies a scalar data type, the function is a scalar-valued function. You can use multiple Transact-SQL statements to define scalar-valued functions.
If the RETURNS clause specifies table, the function is a table-valued function. According to the definition of function body, table-valued function can be divided into inline function or multiple-statement function.
If the table specified by the RETURNS clause does not contain a list of columns, the function is an inline function. An inline function is a table-valued function defined using a single SELECT statement that forms the body of a function. The column of the table returned by the function, including the data type, comes from the select list of the SELECT statement that defines the function.
If the table type specified by the RETURNS clause has a column and its data type, the function is a multiple-statement table-valued function.
The following statement is allowed in the body of a multiple-statement function. Statements that are not listed in the following list cannot be used in the body of a function.
An assignment statement.

Control flow statements.

DECLARE statement that defines the data variables and cursors for the local function.

SELECT statement, which contains a selection list with an expression that assigns a value to a function's local variable.

A cursor operation that references a local cursor declared, opened, closed, and disposed in a function. Only fetch statements that use an INTO clause to assign values to a local variable are allowed, and FETCH statements that return data to the client are not allowed.

INSERT, UPDATE, and DELETE statements, which modify the local table variables of the function.

The EXECUTE statement invokes the extended stored procedure.
Deterministic and side effects of functions
The function can be either determined or indeterminate. If the result is always the same when the function is invoked with a specific set of input values, the functions are determined. If you use the same set of specific input values each time the function is invoked, and the results returned are always different, the functions are indeterminate.
An indeterminate function can have a side effect. Side effects are changing some of the global state of the database, such as updating a database table or some external resources, such as a file or network (for example, modifying a file or sending an e-mail message).
Built-in indeterminate functions in the body of a user-defined function are not allowed;

@ @CONNECTIONS	@ @TOTAL_ERRORS
@ @CPU_BUSY	@ @TOTAL_READ
@ @IDLE	@ @TOTAL_WRITE
@ @IO_BUSY	GETDATE
@ @MAX_CONNECTIONS	getUTCDate
@ @PACK_RECEIVED	NEWID
@ @PACK_SENT	RAND
@ @PACKET_ERRORS	Textptr
@ @TIMETICKS

Although indeterminate functions are not allowed in the body of a user-defined function, these user-defined functions can still have side effects when calling an extended stored procedure.

Because extended stored procedures can have side effects on the database, the function that invokes the extended stored procedure is indeterminate. When a user-defined function call extends stored procedures that have side effects on the database, do not expect the result set to remain consistent or perform functions.

Calling an extended stored procedure from a function
Extended stored procedures cannot return a result set to the client when called from within the function. Any ODS APIs that return a result set to the client will return FAIL. Extended stored procedures can be connected 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 wake-up calls from batches or stored procedures, extended stored procedures are executed in the context of the Windows® security account running SQL Server. The owner of the stored procedure should consider this when granting user EXECUTE privileges.

function call
Scalar-valued functions can be invoked at locations where scalar expressions can be used, including computed columns and CHECK constraint definitions. When invoking a scalar-valued function, you should at least 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 certainty of that function also determines whether the index can be created on the computed column. You can create an index on a computed column that uses the function only if the function is deterministic. If the function always returns the same value when the input is the same, the function is deterministic.

You can invoke a table-valued function by using a name that is part of it.

[database_name.] [Owner_name.] Function_name ([argument_expr][,...])

For system table functions contained in Microsoft®sql server™2000, the wake-up call is preceded by a prefix of the function name "::".

SELECT *
From:: Fn_helpcollations ()

Transact-SQL errors that cause the statement to stop executing and then continue from the next statement in the stored procedure are handled differently in the function. In a function, this type of error causes the function to stop executing. This in turn causes the statement that invokes the function to stop executing.

Permissions
The user should have the CREATE FUNCTION permission to execute the CREATE FUNCTION statement.

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

The owner of the function has EXECUTE permission on its function. Other users do not have execute permission unless they are granted execute permission on a specific function.

To create or change tables that reference user-defined functions in CONSTRAINT, DEFAULT clauses, or computed column definitions, users must also have REFERENCES permissions on these functions.

Example
A. Scalar-valued user-defined functions for the calculation of the ISO week
In the following example, the user-defined function Isoweek takes the date parameter and calculates the ISO week number. In order to correctly evaluate the function, you must invoke SET Datefirst 1 before calling the function.

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 (DATEPART (yy, @DATE)-1 as 
char (4)) + ' + ' + 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)) >=) 
SET @ISOweek =1 return 
(@ISOweek) End 

The following is a function call. Note that the Datefirst is set to 1.

SET Datefirst 1
SELECT master.dbo.ISOweek (' 12/26/1999 ') as ' ISO Week '
The following is the result set.

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

B. Inline table-valued functions
The following example returns an inline table-valued function.

Use the pubs go 
CREATE FUNCTION salesbystore (@storeid varchar) RETURNS the 
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
Suppose you have a table that represents the following hierarchical relationships:

CREATE TABLE Employees (Empid nchar (5) PRIMARY KEY, 
empname nvarchar (m), 
Mgrid nchar (5) REFERENCES Employees ( Empid), 
title nvarchar (30) 

The table-valued function, Fn_findreports (INEMPID), has a given employee ID that returns a table that corresponds to all employees who report directly or indirectly to a given employee. This logic cannot be represented in a single query, but it can be implemented 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