User define function (UDF) in sql server

Function is a database object in Sql Server. User define functions are routines, method or protocol which performs calculations, actions such as complex calculations (find the age or some mathematical calculation) and return the result of that action. Function may or may not have parameters, but always either a scalar value or a result set. The return value can be any data type except text, Image, ntext, cursor and timestamp.

There are three Types of User Define Function UDFS in Sql Server:

1. Scalar
2. Inline Table-Valued
3. Multi-statement Table-Valued

Scalar function

A Scalar UDF can accept zero or many input parameter and will return a single value. A Scalar user-defined function returns one of the scalar (int, char, varchar etc.) data types. Text, ntext, image and timestamp data types are not supported. 

Let’s start step by step to create User Define Functions.

Here I am creating a simple function. In this function I am passing date and returning the year of passing date.

CREATE FUNCTION dbo.fnGet_Year(@Date AS DateTime) RETURNS INT AS BEGIN     RETURN YEAR(@Date) END

Function created successfully.

We can call this function using select statement.

Inline Table-Valued

An inline table-valued function returns a variable of data type table whose value is derived from a single SELECT statement. Since the return value is derived from the SELECT statement, there is no BEGIN/END block needed in the CREATE FUNCTION statement. There is also no need to specify the table variable name (or column definitions for the table variable) because the structure of the returned value is generated from the columns that compose the SELECT statement. Because the results are a function of the columns referenced in the SELECT, no duplicate column names are allowed and all derived columns must have an associated alias.

It return a table data type. Inline functions can be used to achieve the functionality of parameterized views.

Example

Here I am creating Inline Table – valued function

CREATE FUNCTION dbo.fn_Get_EmpNames_By_Area                  ( @eadd nvarchar(50) ) RETURNS table AS RETURN (         SELECT * from EMP         WHERE empAdd = @eadd        );

Function created successfully.

Now I am calling this function

  

It returns the list of employee which area is pune.

Multi-statement Table-Valued

A Multi-Statement Table-Valued user-defined function returns a table. It can have one or more than one T-Sql statement. Within the create function command you must define the table structure that is being returned.

See the example

CREATE FUNCTION [dbo].[Get_Gender_Wise_Records] (        @Gender varchar(10) ) RETURNS @Gender_Wise_Records TABLE (        EmpID varchar(50) not null,                    JobTitle varchar(50),                    MaritalStatus varchar(10) ) AS BEGIN         IF (@Gender = 'M')               BEGIN                      INSERT INTO @Gender_Wise_Records                      SELECT BusinessEntityID,JobTitle,MaritalStatus                      FROM Employee                      WHERE Gender='M'               END        ELSE               BEGIN                      INSERT INTO @Gender_Wise_Records                      SELECT BusinessEntityID,JobTitle,MaritalStatus                      FROM Employee                      WHERE Gender='F'               END        RETURN ; END

Function is created successfully.

Now I am executing this function.

Benefits of User Define Function

1.       Reusable

2.       Faster

3.       Easy to maintain

4.       Reduce network traffic

Drawback of User Define function

1.       Function is not used to Insert, Update, Delete data in database.

2.       Can’t modify the state of the database.

3.       UDF does not support Try-catch, @ERROR or RAISERROR function.

4.       Function can be nested up to 32 levels.

5.       UDF can’t return XML data type

6.       UDF can have up to 1023 input parameters.

7.       Does not support Row Count.

8.       Doesn't support Exception handling.