web training room for online courses
Learn SQL Database Development, MS SQL Tutorial Online

UDF (User define function) in SQL Server

What is UDF in SQL Server

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.

SQL UDF Example (return type table)

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())
SQL UDF Example (return type scalar value)

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
Comment
Name Email Website
Subscribe
 
UDF in SQL Server

SQL User define function
Consulting