Similar to the stored procedures, you can also create functions to store a set of T-SQL statements permanently. These functions are also referred to as user-defined functions (UDFs). A UDF is a database object that contains a set of T-SQL statements, accepts parameters, performs an action, and returns the result of that action as a value. The return value can either be a single scalar value or a result set.
UDFs have a limited scope as compared to stored procedures. You can create functions in situations when you need to implement a programming logic that does not involve any permanent changes to the database objects outside the function. For example, you cannot modify a database table from a function.
UDFs are of different types: scalar functions and table-valued function. As a database developer, it is important for you to learn to create and manage different types of UDFs.
To create a function, you can use th CREATE FUNCTION statement. The syntax of the CREATE FUNCTION statement is:
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [AS ] [ type_schema_name. ]
Parameter_data_type] }
[ = default ] }
[, …n ]
]
)
RETURNS return_data_type
[WITH <function_option> [ , . . .n ] ]
[ AS ]
BEGIN
Function_body
RETURN expression
END
[;]
Where,
UDFs have a limited scope as compared to stored procedures. You can create functions in situations when you need to implement a programming logic that does not involve any permanent changes to the database objects outside the function. For example, you cannot modify a database table from a function.
UDFs are of different types: scalar functions and table-valued function. As a database developer, it is important for you to learn to create and manage different types of UDFs.
Creating UDFs
A UDF contains the following components:- Function name with optional schema/owner name
- Input parameter name and data type
- Options applicable to the input parameter
- Return parameter data type and optional name
- Options applicable to the return parameter
- One or more T-SQL statements
To create a function, you can use th CREATE FUNCTION statement. The syntax of the CREATE FUNCTION statement is:
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [AS ] [ type_schema_name. ]
Parameter_data_type] }
[ = default ] }
[, …n ]
]
)
RETURNS return_data_type
[WITH <function_option> [ , . . .n ] ]
[ AS ]
BEGIN
Function_body
RETURN expression
END
[;]
Where,
- Schema_name is the name of the schema to which the UDF belongs.
- Function_name is the name of the UDF. Function names must comply with the rules for identifiers and must be unique within the database and to its schema.
- @parameter_name is a parameter in the UDF. One or more parameters can be declared.
- [type_schema_name.] parameter_data_type is the data type of the parameter, and optionally the schema to which it belongs.
- [=default ] is a default value for the parameter.
- Return_data_type is the return value of a scalar user-defined function.