Online Training for Asp.net MVC Web Designing Development, MS SQL, Digital Marketing

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
Group Training