User-defined function (UDF) in SQL Server is just like a function in any other programming language, In SQL we can write a program that accepts parameters, and has some return type as result.
We learn two types of UDFs: table-valued and scalar-valued.
Here are few scenarios where UDF is the right solution:
This Table-valued function will return a table, list of employees who left the organization for a particular date range,
the UDF has two parameters startDate and tillDate
create FUNCTION [udfGetEmployeesOnLeave] ( @startDate datetime ,@tillDate datetime ) RETURNS table AS return ( Select EmployeeId,FirstName,LastName,DOB from tbEmployee where Leavingdate between @startDate and @tillDate )
This is how you can execute the function in sql query, or you can call from any stored procedure
SELECT * FROM [DBName].[dbo].[udfGetEmployeesOnLeave] ('2016-10-08',getdate())
This is an example of scalar-valued function that returns the count of employees for any date range.
This UDF takes two parameters startDate and tillDate, and returns an integer number
create FUNCTION udfGetEmployeeJoined ( @startDate datetime ,@tillDate datetime ) RETURNS bigint AS BEGIN declare @totalEmployee bigint Select @totalEmployee= Count(FirstName) from tbEmployee where JoiningDate between @startDate and @tillDate -- Return the result of the function RETURN @totalEmployee END