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’);