Create function sqlserver User-Defined FUNCTION

Create a user-defined function, which is a stored Transact-SQL routine of the returned value. User-defined functions cannot be used to modify the global database status. Like system functions, user-defined functions can be called from queries. You can also EXECUTE an EXECUTE statement like a stored procedure.
User-Defined Functions are modified using alter function and removed 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

Embedded Table value 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 value 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])

Parameters
Owner_name
The name of the user ID that owns the User-Defined Function. Owner_name must be an existing user ID.
Function_name
The name of the User-Defined Function. The function name must comply with the identifier rules. For its owner, the name must be unique in the database.
@ Parameter_name
User-Defined function parameters. One or more parameters can be declared in the create function statement. A function can have a maximum of 1,024 parameters. During function execution, the value of each declared parameter must be specified by the user, unless the default value of this parameter has been defined. If the function parameter has a default value, you must specify the "default" keyword when calling the function to obtain the default value. This behavior is different from the parameter with default values in the stored procedure. Omitting a parameter in the stored procedure also means that the default value is used.
Use the @ symbol as the first character to specify the parameter name. The parameter name must comply with the identifier rules. Each function parameter is only used for this function. The same parameter name can be used in other functions. Parameters can only replace constants, but cannot replace the names of table names, column names, or other database objects.
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. The timestamp data type and user-defined data type are not supported. You cannot specify non-standard data types (such as cursor and table ).
Scalar_return_data_type
Is the return value of a scalar user-defined function. Scalar_return_data_type can be any scalar data type supported by SQL Server (except for text, ntext, image, and timestamp ).
Scalar_expression
Specify the scalar value returned by the scalar function.
TABLE
Specify the return value of the Table value function as the table.
In an embedded TABLE value function, a single SELECT statement is used to define the TABLE return value. Embedded functions do not have associated return variables.
In a multi-statement TABLE valued function, @ return_variable is a TABLE variable used to store and accumulate rows that should be returned as function values.
Function_body
Specify the values of functions defined by a series of Transact-SQL statements. These statements together will not produce side effects. Function_body is only used for scalar functions and multi-statement Table value functions.
In scalar functions, function_body is a series of Transact-SQL statements that combine to obtain scalar values.
In a multi-statement Table value function, function_body is a series of Transact-SQL statements that fill the table return variables.
Select-stmt
Is a single SELECT statement that defines the return values of nested table valued functions.
ENCRYPTION
Indicates that SQL Server encrypts the system table columns that contain the CREATE FUNCTION statement text. Use ENCRYPTION to avoid publishing functions as part of SQL Server replication.
SCHEMABINDING
Bind the function to the database object referenced by the function. If a function is created using the SCHEMABINDING option, you cannot change (using the ALTER statement) or remove (using the DROP statement) The database objects referenced by the function.
The binding relationship between a Function and Its referenced object is unbound only when the following two conditions occur:
Except the function.

The function (using the ALTER statement) was changed without the SCHEMABINDING option specified ).
The function can be bound to the architecture only when the following conditions are met:
The user-defined functions and views referenced by this function are also bound to the architecture.

The object referenced by this function is not referenced by two part names.

The Function and Its referenced objects belong to the same database.

The user executing the create function statement has the REFERENCES permission on all database objects referenced by the FUNCTION.
If the preceding conditions are not met, the create function statement with the SCHEMABINDING option specified will fail.
Note
User-Defined Functions are scalar functions or table-valued functions. If the RETURNS clause specifies a scalar data type, the function is a scalar value function. You can use multiple Transact-SQL statements to define scalar value functions.
If the RETURNS clause specifies a TABLE, the function is a TABLE value function. According to the definition of the function subject, table valued functions can be divided into intra-row functions or multi-statement functions.
If the TABLE specified by the RETURNS clause does not contain a list of columns, this function is a row function. An in-row function is a table-valued function defined by a single SELECT statement, which forms the main body of the function. The columns (including data types) in the table returned by the function are the SELECT list of SELECT statements that define the function.
If the TABLE type specified by the RETURNS clause contains columns and their data types, this function is a multi-statement TABLE value function.
The following statements can be used in the body of a Multi-statement function. Statements not listed in the following list cannot be used in the function body.
Value assignment statement.

Control Flow statement.

DECLARE statement, which defines the local data variables and cursors of the function.

SELECT statement, which contains a selection list with an expression. The expression in the list grants the value to the local variable of the function.

A cursor operation that references a local cursor declared, opened, closed, and released in a 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. These statements modify the local table variables of the function.

EXECUTE statements call extended stored procedures.
Certainty and side effects of functions
The function can be definite or uncertain. If the returned results are always the same when a function is called with a specific set of input values, these functions are definite. If the same set of specific input values are used for each function call and the returned results are always different, these functions are uncertain.
Uncertain functions produce side effects. The side effect is to change the global status of the database, such as updating the database table or some external resources, such as files or networks (such as modifying files or sending email messages ).
Built-in uncertain functions are not allowed in the User-Defined Function body. These Uncertain functions are as follows:

@ 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 uncertain functions are not allowed in the User-Defined Function body, these user-defined functions still produce side effects when calling the extended stored procedure.

Because the extended stored procedure has a side effect on the database, it is uncertain to call the extended stored procedure function. When you define a function call to extend the stored procedure that has database side effects, do not expect the result set to be consistent or execute the function.

Call extended stored procedures from functions
The extended stored procedure cannot return the result set to the client when calling a function. Any ods api that returns the result set to the client will return FAIL. The extended stored procedure can be connected back to Microsoft SQL Server. However, it should not try to join the same transaction as the function that calls the extended stored procedure.

Similar to waking up a call from a batch or stored procedure, an extended stored procedure is executed in the context of a Windows security account running SQL Server. The owner of a stored procedure should consider this when granting the user the EXECUTE privilege.

Function call
Where a scalar expression can be used, you can wake up and call scalar value functions, including calculation column and CHECK constraint definitions. When a scalar value function is called, at least two part names of the function should be used.

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

If a user-defined function is used to define a computing column, the certainty of the function also determines whether an index can be created on the computing column. You can create an index on the computed column of a function only when the function is deterministic. If the function always returns the same value when the input is the same, the function is deterministic.

You can use a part of the name to wake up calling the table value function.

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

For system table functions contained in Microsoft SQL Server 2000, you must add the prefix ":" Before the function name during wake-up "::".

SELECT *
FROM: fn_helpcollations ()

For the Transact-SQL errors that cause the statement to stop execution and continue execution from the next statement in the stored procedure, the processing method in the function is different. In a function, such errors cause the function to stop running. This in turn causes the wake-up statement that calls the function to stop execution.

Permission
The user shall have the create function permission to execute the create function statement.

By default, create function permissions are granted to members of sysadmin fixed server roles and db_owner and db_ddladmin fixed database roles. Members of sysadmin and db_owner can use the GRANT statement to GRANT the create function permission to other logon users.

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

To create or modify a table that REFERENCES user-defined functions in the CONSTRAINT, DEFAULT clause, or calculation column definition, you must have the REFERENCES permission on these functions.

Example
A. Calculate the ISO week Scalar Value User-Defined Function
In the following example, the User-Defined Function ISOweek takes the date parameter and calculates the ISO week number. To calculate the function correctly, you must wake up and call set datefirst 1 before calling the function.Copy codeThe Code is as follows: 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) + '123 ')
-- 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

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

Set datefirst 1
SELECT master. dbo. ISOweek ('2014/1/123') AS 'iso Week'
The following is the result set.

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

B. Embedded Table value functions
In the following example, the embedded Table value function is returned.Copy codeThe Code is as follows: 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 value functions
Assume that a table represents the following hierarchies:Copy codeThe Code is as follows: create table employees (empid nchar (5) primary key,
Empname nvarchar (50 ),
Mgrid nchar (5) REFERENCES employees (empid ),
Title nvarchar (30)
)

The table value function fn_FindReports (InEmpID) has a given employee ID, which returns the table corresponding to all employees who report directly or indirectly to a given employee. This logic cannot be expressed in a single query, but can be implemented as user-defined functions.Copy codeThe Code is as follows: 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 ('123 ')
GO