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:
- 1. Whenever some calculation require in any program based on some input parameters, we can write a UDF to do that and return a scalar value.
- 2. UDF also helps to achieve Single Responsibility Principal (often known as SRS) in programming, so instead of writing a big set of logic in one place, we split them into multiple single calculations, which will be more easy to read and maintain.
- 3. Better error handling, if we have multiple UDFs in a big program, it will be easy to catch the error when anything goes wrong.
- 4. Reusability of code, if an UDF strictly follows SRS Principal, then can be utilized from multiple different programs.
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]
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
declare @totalEmployee bigint
Select @totalEmployee= Count(FirstName) from tbEmployee where
JoiningDate between @startDate and @tillDate
-- Return the result of the function