User-defined functions in SQL Server

User Defined Functions In SQL Server allow for a modular type of programming, where code and logic can be included inside the function.  Functions are T-SQL code that can accept parameters, perform logic and complex calculations,
and return data. Scalar functions return a single value, and table-valued functions return a result set.

There are two types of user defined function :

  • Scalar function
  • Table valued function

Scalar function : a user defined function that return the single value.

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
[ = default ] [ READONLY ] }
[ ,…n ]
]
)
RETURNS return_data_type
[ WITH <function_option> [ ,…n ] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
[ ; ]

The only items that you must specify are function_name, the RETURNS data type, and the BEGIN…END code block that contains the actual T-SQL code. 

 

Parameterizing functions : The input value that can be passed from calling function from the code. A parameter can be set to a constant, a column from a table, an expression, and other values. Functions can contain three types of parameters.

  • Input : The value pass into body of the function.
  • Optional : This parameter is not required to execute the function.
  • Default :  when a value is assigned to the parameter during creation.

Example:

  • If function have multiple parameter  and it have optional and input parameter its good if we supply the value to optional parameter or always correct the order of the parameter. And it’s good to  use the name of parameter. 

Table-valued functions :  table valued function return a result set. Similar to select from  table or view.  there are two type of table valued function both return the complete result set.

  • Inline : The inline function simply returns a result set.
  • Multistatement : Multistatement function offers the ability to include logic within the body of the function

 

Example:

ENCRYPTION : This option used to encrypt the T-SQL of function, user can not view Script using sp_helptext when this option is used.

SCHEMABINDING : Specifies that the function is bound to the database objects that it references. When SCHEMABINDING is specified, the base objects cannot be modified in a way that would affect the function definition.

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

Specifies the OnNULLCall attribute of a scalar-valued function. If not specified, CALLED ON NULL INPUT is implied by default. This means that the function body executes even if NULL is passed as an argument.

EXECUTE AS Clause : Specifies the security context under which the user-defined function is executed. Therefore, you can control which user account SQL Server uses to validate permissions on any database objects that are referenced by the function.

EXECUTE AS cannot be specified for inline user-defined functions.

Limitations of User defined function:

  • User-defined functions cannot be used to perform actions that modify the database state.
  • User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target.
  • User-defined functions can be nested up to 32 levels.