In this tutorial you will learn how to write user defined function, we often call as UDF function in MS SQL, a SQL object created by developer which returns some values based on input parameters.
SQL UDF (User defined function) 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 functions.
Here are few scenarios where UDF is the right solution:
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.
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.
Better error handling, if we have multiple UDFs in a big program, it will be easy to catch the error when anything goes wrong.
Reusability of code, if an UDF strictly follows SRS Principal, then can be utilized from multiple different programs.
This Table-valued UDF 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())
The above UDF function will return a table value based on input parameters value.
This is an example of scalar-valued UDF 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
As you can in both above examples, input parameters are mandatory, that means wherever we want to call those udf functions, we must provide the input parameters value.
However, we can define optional input parameters in sql udf function, which means without providing the input parameters value we can call the udf in sql statement.
Here is an example of how to define udf with optional input parameters.
Create FUNCTION udfCalculateDiff( @num1 int = 6, @num2 int =4 ) RETURNS int AS BEGIN DECLARE @total INT SET @total = @num1 + @num2 RETURN @total END
To call the above udf we need to use default keyword, look at below example.
select dbo.udfCalculateDiff (default, default) select dbo.udfCalculateDiff (20, 80)
This may look not much useful, but that’s how it works in sql.