User Defined Function (UDF) is a single or collection of pre prepared T-SQL statements under a common name. It allows us to create T-SQL statements under common name that can accept zero or more parameter and may return scalar data value or table data value. In simple UDF is code of segment accepting the argument, process the logic and return the value.
There are three types of UDF
- Scalar
- Inline Table
- Multistatement table
Let see one by one
Scalar Function
Scalar UDF return a single value consisting of a scalar datatype such as integer, varchar(n), char(n), money, datetime, bit, and so on. UDFs can also return user-defined datatypes (UDDTs) if they are based on a scalar datatype.
Syntax
CREATE FUNCTION owner.function_name
(parameter_name data_type [=default] [, n])
RETURNS scalar_data_type
[WITH function_option]
AS BEGIN
Function body
RETURN scalar expression
END
Department
Take this table as example I created one function to find whether the employee needs to pay tax or not based on the gross salary then we can go for the UDF.
ID | Department | Basic | HRA | MA | PF |
1 | IT | 20000 | 6000 | 800 | 2000 |
2 | Sales | 10000 | 3000 | 400 | 1000 |
3 | Add | 25000 | 7500 | 1000 | 2500 |
4 | IT | 30000 | 9000 | 1200 | 3000 |
5 | IT | 25000 | 7500 | 1000 | 2500 |
6 | Sales | 5000 | 1500 | 200 | 500 |
7 | Add | 16533 | 4960 | 661 | 1653 |
8 | Sales | 4562 | 1369 | 182 | 456 |
Take this table as example I created one function to find whether the employee needs to pay tax or not based on the gross salary then we can go for the UDF.
Create Function PayTax(@id int) Returns Varchar(50) As
Begin
Declare @bool Varchar(10)
Declare @grosssalary Int
Select @grosssalary = ((basic + hra +ma)-(pf + loan)) From department Where employeeid= @id
If @grosssalary >10000
Set @bool ='Yes'
Else
Set @bool ='No'
Return @bool
End
In this example I have created fucntion with name of PayTax with one argument called @id and inside the function I have declared two varibles @bool and @grosssalary. @bool is used to store whether the employee need to pay tax or not that means ‘Yes’ or ‘No’ and @grosssalary is used to store the gross pay value inside the function finally it return the value of varchar() datatype. This kind of scenarion you can use the UDF.
To call this function you can use
select dbo.Paytax(1)
To drop the function using this line of code
drop function paytax
Inline Table
This type of function returns a result set, much like a view. However, unlike a view, functions can accept parameters. The inline function’s syntax is quite simple. In the function definition, the return type is set to a table. A RETURN statement is used with a SELECT query in parentheses
Syntax
CREATE FUNCTION owner.function_name
(parameter_name data_type [=default] [, n])
RETURNS Table
[WITH function_option]
AS
RETURN (
Function body statement
)
Employee Table
EmployeeID | EmployeeName | Address |
1 | A | Arumandhai |
2 | B | Madurai |
3 | C | Trichy |
4 | D | Chennai |
5 | E | madurai |
6 | F | Kovai |
7 | G | Kovai |
8 | H | Chennai |
Department Table
Take the above tables as example. I create a function with Id as parameter and it will return the data row which is matching to that Id.
ID | Department | Basic | HRA | MA | PF |
1 | IT | 20000 | 6000 | 800 | 2000 |
2 | Sales | 10000 | 3000 | 400 | 1000 |
3 | Add | 25000 | 7500 | 1000 | 2500 |
4 | IT | 30000 | 9000 | 1200 | 3000 |
5 | IT | 25000 | 7500 | 1000 | 2500 |
6 | Sales | 5000 | 1500 | 200 | 500 |
7 | Add | 16533 | 4960 | 661 | 1653 |
8 | Sales | 4562 | 1369 | 182 | 456 |
Take the above tables as example. I create a function with Id as parameter and it will return the data row which is matching to that Id.
Create Function GetEmployee( @id int) Returns Table
As
Return
(
Select E.EmployeeID, E.EmployeeName, E.Address,D.Department, D.Basic
From Employee as E inner join Department as D on E.EmployeeID=D.EmployeeID
Where E.EmployeeID= @id
)
To run the function
Select * from GetEmployee(1)
The Output will be
EmployeeID | EmployeeName | Address | Department | Basic | EmployeeID |
1 | A | Arumandhai | IT | 20000 | 1 |
Multi-Statement Table
Multi - statement functions can be used to do some very unique things outside the context of a standard SELECT statement. In simple sometimes you need result, but you can’t achieve with simple select statement for that you need to do some more complex logic, that time you can use Multi-statement Table. As with the preceding inline function, this type of function also returns a table - type result set, but the table is explicitly constructed in script.
Syntax
CREATE FUNCTION owner.function_name
(parameter_name data_type [=default] [, n])
RETURNS @table_variable_name Table(columnname1 datatype,
columnname2 datatype,
columnname3 datatype,....)
[WITH function_option]
AS BEGIN
Function body
RETURN Table expression
END
Take Employee Table and Department Table as example. I created one function that will return a table as result set based on the parameter and condition which is implemented inside the function. For instance if I pass the null value as parameter then it should return all rows or if I pass normal value as parameter then it should return row which is matching to parameter.
Create Function GetEmployee(@Id int)
Returns @Employee Table(ID int,
EmpName Varchar(50),
Address varchar(50))
As
Begin
If @Id is null
Begin
Insert into @Employee(ID,EmpName,Address)
Select EmployeeID,EmployeeName,Address from Employee
End
Else
Begin
Insert into @Employee(ID,EmpName,Address)
Select EmployeeID,EmployeeName,Address from Employee
Where EmployeeID=@id
End
Return
End
If I call the function using
Select * from GetEmployee(null)
Output will be
EmployeeID | EmployeeName | Address |
1 | A | Arumandhai |
2 | B | Madurai |
3 | C | Trichy |
4 | D | Chennai |
5 | E | madurai |
6 | F | Kovai |
7 | G | Kovai |
8 | H | Chennai |
If I call the function using
Select * from GetEmployee(2).
The output will be
EmployeeID | EmployeeName | Address |
2 | B | Madurai |
The examples which I have given are very simple. you can create your own function with much more complex and logic.
I hope this article will be very helpful to the beginners.Thanks for reading.
“Keep reading and share the knowledge”
“Grow more trees to Save the Earth”
No comments:
Post a Comment