User defined function is T-SQL (Transact SQL) or CLR (Common Language Runtime) routine, Which accepts parameters and perform complex calculation and returns result in the form of Scalar Value or table value.
When we invoke a function every time it is compiled and executed.
We can't use output parameters with function like in stored procedures.
When we invoke a function every time it is compiled and executed. We can't use DML(INSERT/UPDATE/DELETE) statements. Therefore we can't use transactions with in function.
It can be invoked from procedure.
We can't use try catch block with in functions.
Scalar Function
We can create scalar function as follows.
Lets Calculates salary after pf deduction
only if salary is greater than 15000. We will pass two input parameters for salary and pf percentage.
CREATE FUNCTION dbo.fnCalculatateSalaryWithPFDeduction
(
@salary decimal(7,2),
@percentage decimal(5,2)
)
RETURNS decimal(7,2)
AS BEGIN
DECLARE @NetSal decimal(7,2) = @salary
IF(@salary>15000)
SET @NetSal = @salary-(@salary*@percentage)/100
RETURN @NetSal
END
Go
Here dbo is schema.
We can test the function with static parameters as follows.
--Test Case With Salary 16000
SELECT dbo.fnCalculatateSalaryWithPFDeduction(16000,12.5) as [NetSalary]
Go
--OUTPUT: NetSalary ------------- 14000.00 (1 row affected)
--Test Case With Salary 13000
SELECT dbo.fnCalculatateSalaryWithPFDeduction(13000,12.5) as [NetSalary]
Go
--OUTPUT: NetSalary ------------- 13000.00 (1 row affected)
Using Scalar Function as Sub-Query
Create table tbEmployeePayroll in dbo schema.
CREATE TABLE dbo.tbEmployeePayroll
(
EmployeeId int identity Constraint PK_tbEmployeePayroll_EmployeeId Primary Key,
BasicSalary decimal(7,2)
)
Go
Insert 10 Payroll Records for Employees using While loop and RAND function.
DECLARE @c int = 1
WHILE @c<10
BEGIN
INSERT INTO dbo.tbEmployeePayroll(BasicSalary) VALUES(RAND()*100000)
SET @c += 1
END
Go
List All Employees
SELECT * FROM dbo.tbEmployeePayroll
Go
--OUTPUT: EmployeeId BasicSalary ----------- -------------- 1 6666.97 2 40268.57 3 32382.02 4 45021.26 5 56214.14 6 95549.75 7 90287.71 8 59821.74 9 47102.78 (9 rows affected)
Apply function to get NetSalary of each employee after pf deduction with Employees having BasicSalary greater than 15000.
SELECT
EmployeeId,
BasicSalary,
(BasicSalary-dbo.fnCalculatateSalaryWithPFDeduction(BasicSalary,12.47)) PFAmount,
dbo.fnCalculatateSalaryWithPFDeduction(BasicSalary,12.47) as NetSalary
FROM dbo.tbEmployeePayroll
Go
--OUTPUT: EmployeeId BasicSalary PFAmount NetSalary ----------- -------------- ----------- ------------ 1 6666.97 0.00 6666.97 2 40268.57 5021.49 35247.08 3 32382.02 4038.04 28343.98 4 45021.26 5614.15 39407.11 5 56214.14 7009.90 49204.24 6 95549.75 11915.05 83634.70 7 90287.71 11258.88 79028.83 8 59821.74 7459.77 52361.97 9 47102.78 5873.72 41229.06 (9 rows affected)
As scalar function will returns only one value, How we can returns more than one values by scalar function.
We can concatenate values and returned by function.
Alter Function - Alter Function Statement is used to modify existing functionALTER FUNCTION dbo.fnCalculatateSalaryWithPFDeduction
(
@salary decimal(7,2),
@percentage decimal(5,2)
)
RETURNS VARCHAR(20)
AS BEGIN
DECLARE @NetSal decimal(7,2) = @salary
DECLARE @pfAmt decimal(7,2) = 0
IF(@salary>15000)
BEGIN
SET @pfAmt = (@salary*@percentage)/100
SET @NetSal = @pfAmt
END
RETURN CAST(@NetSal AS VARCHAR(9))+'@'+CAST(@pfAmt AS VARCHAR(9))
END
Go
Table Value Function
But again how we can get employee salary variations with different pf rates. we needs to create a function, which will return table.
CREATE FUNCTION dbo.fnSalaryVariancesWithPF
(
@Salary decimal(7,2)
)
RETURNS @SalStmt Table
(
PFAmount decimal(7,2),
NetSalary decimal(7,2),
PFRate decimal(5,2)
)
AS BEGIN
DECLARE @c tinyint = 1
DECLARE @p decimal(7,2)=0
WHILE @c<=5
BEGIN
SET @p = (@Salary*(@c+12.47))/100
INSERT INTO @SalStmt Values(@p,@Salary-@p,@c+12.47)
SET @c+=1
END
RETURN
END
GO
SELECT * FROM dbo.fnSalaryVariancesWithPF(14000)
Go
--OUTPUT: PFAmount NetSalary PFRate ------------- --------------- ------- 1885.80 12114.20 13.47 2025.80 11974.20 14.47 2165.80 11834.20 15.47 2305.80 11694.20 16.47 2445.80 11554.20 17.47 (5 rows affected)
CROSS APPLY | OUTER APPLY
We can use cross apply and outer apply statements to join table value function with EmployeePayroll table.
SELECT
EmployeeId,
BasicSalary,
PF.PFRate,
PF.PFAmount,
PF.NetSalary
FROM dbo.tbEmployeePayroll EP
CROSS APPLY dbo.fnSalaryVariancesWithPF(EP.BasicSalary) PF
Go
--OUTPUT: EmployeeId BasicSalar PFRate PFAmount NetSalary ----------- ------------- ------------ ---------------- --------- 1 6666.97 13.47 898.04 5768.93 1 6666.97 14.47 964.71 5702.26 1 6666.97 15.47 1031.38 5635.59 1 6666.97 16.47 1098.05 5568.92 1 6666.97 17.47 1164.72 5502.25 2 40268.57 13.47 5424.18 34844.39 2 40268.57 14.47 5826.86 34441.71 2 40268.57 15.47 6229.55 34039.02 2 40268.57 16.47 6632.23 33636.34 2 40268.57 17.47 7034.92 33233.65 3 32382.02 13.47 4361.86 28020.16 3 32382.02 14.47 4685.68 27696.34 3 32382.02 15.47 5009.50 27372.52 3 32382.02 16.47 5333.32 27048.70 3 32382.02 17.47 5657.14 26724.88 4 45021.26 13.47 6064.36 38956.90 4 45021.26 14.47 6514.58 38506.68 4 45021.26 15.47 6964.79 38056.47 4 45021.26 16.47 7415.00 37606.26 4 45021.26 17.47 7865.21 37156.05 5 56214.14 13.47 7572.04 48642.10 5 56214.14 14.47 8134.19 48079.95 5 56214.14 15.47 8696.33 47517.81 5 56214.14 16.47 9258.47 46955.67 5 56214.14 17.47 9820.61 46393.53 6 95549.75 13.47 12870.55 82679.20 6 95549.75 14.47 13826.05 81723.70 6 95549.75 15.47 14781.55 80768.20 6 95549.75 16.47 15737.04 79812.71 6 95549.75 17.47 16692.54 78857.21 7 90287.71 13.47 12161.75 78125.96 7 90287.71 14.47 13064.63 77223.08 7 90287.71 15.47 13967.51 76320.20 7 90287.71 16.47 14870.39 75417.32 7 90287.71 17.47 15773.26 74514.45 8 59821.74 13.47 8057.99 51763.75 8 59821.74 14.47 8656.21 51165.53 8 59821.74 15.47 9254.42 50567.32 8 59821.74 16.47 9852.64 49969.10 8 59821.74 17.47 10450.86 49370.88 9 47102.78 13.47 6344.74 40758.04 9 47102.78 14.47 6815.77 40287.01 9 47102.78 15.47 7286.80 39815.98 9 47102.78 16.47 7757.83 39344.95 9 47102.78 17.47 8228.86 38873.92 (45 rows affected)
We can delete function as follows.
DROP FUNCTION dbo.fnSalaryVariancesWithPF
Go
If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.
Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.