Free Web Development Online Tutorials, Learn to Code
User Defined Functions in SQL example, advantages of udf in sql server, Create a udf in sql server

UDF (User defined functions) in SQL Server

In this tutorial you will learn how to write user defined function UDF in SQL

What is UDF in SQL Server

UDF (User-defined function) 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.

Advantages of UDF in SQL server

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 a udf in sql server with return type table

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 UDF in sql query, or you can call UDF from 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 a udf in sql server with return type integer

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