Two types of functions supported by sql server, User defined function and Built-in or system defined function.

User defined function

You can create your custom function to perform any activity in sql server that known as User defined function. Scalar and table valued function is another type of user defined function.

Scalar Functions

In Scalar Function, Data returns by function is always single value defined while function definition with return clause example: CREATE FUNCTION fn_name() RETURNS int. It will always return integer value.

Inline Scalar Function doesn’t have function body and it returns table.

Multi statement function contains multi t-sql command under BEGIN and END block and return single value. Function does not support text, ntext, image cursor and timestamp data type in return value.

Example of multistatement function, here we are creating function to add to numbers

CREATE FUNCTION ufn_add(@num1 int, @num2 int)
RETURNS int
AS
— Returns the stock level for the product.
BEGIN
DECLARE @result int;

IF (@num1 is null and @num2 is null)
BEING
SET @result = 0
RETURN @result
END
ELSE
SET @result = @num1 + @num2
RETURN @result;
END
END;
GO

Now to execute this function use select ufn_add(2,3), Result : 5

 

User-defined table-valued function

Table valued function returns table as output having table as data type, Inline table-valued function has no function body. Result set of single SELECT statement acts as table

Inline table valued function

Example of Inline table-values function, it takes one input parameter and returns a result set of multiple columns.

Transact-SQL

USE testdb;

CREATE FUNCTION ufn_EmployeeDetails (@empid int)

RETURNS TABLE

AS

RETURN

(

SELECT empid, emp_name, address, salary,dept_id

FROM employeeDetails

WHERE empid = @empid

)

Below example will tell you how to invoke the function with input parameter as 1001.

Transact-SQL: SELECT * FROM ufn_EmployeeDetails (1001)

 

Multi Statement table valued function

In Multi statement table valued function defined in BEGIN and END block that contains multiple Transact- SQL statement and inserts result set of multiple queries into table that will be returned.

For example, let’s create a table valued function that takes department id as input and returns list employees that belongs to particular department id.

The function is then invoked specifying employee ID 109.

Transact-SQL

USE testdb

CREATE FUNCTION  ufn_FindEmpdetails (@DeptID int)

RETURNS @resultset TABLE

(

EmpID int primary key NOT NULL,

Emp_Name nvarchar(255) NOT NULL,

Address nvarchar(255) NOT NULL,

Salary int NOT NULL,

DeptId nvarchar(50) NOT NULL

)

AS

BEGIN

 

INSERT INTO @resultset (EmpID,Emp_Name,Address,Salary,DeptId)

SELECT EmpId, Emp_name, Address, Salary, Deptid FROM EmployeeDetails

WHERE Deptid = @DeptID

 

END

Execute the below select query to Invoke the function

Transact-SQL : SELECT EmpID,Emp_Name,Address,Salary,DeptId  FROM ufn_FindEmpdetails (‘IT’);