Creating a Scalar Function with Conditional Logic
Create a Scalar Function with Conditional Logic
Write a SQL query to create a scalar function that calculates a bonus based on employee performance.
Solution:
-- Create a scalar function with conditional logic.
CREATE FUNCTION CalculateBonus
(@PerformanceRating INT, -- Input parameter for performance rating.
@Salary DECIMAL(10, 2)) -- Input parameter for the employee's salary.
RETURNS DECIMAL(10, 2) -- Return type.
AS
BEGIN
DECLARE @Bonus DECIMAL(10, 2);
-- Calculate bonus based on performance rating.
IF @PerformanceRating >= 5
SET @Bonus = @Salary * 0.2; -- 20% bonus for high performers.
ELSE IF @PerformanceRating >= 3
SET @Bonus = @Salary * 0.1; -- 10% bonus for average performers.
ELSE
SET @Bonus = 0; -- No bonus for low performers.
RETURN @Bonus;
END;
Explanation:
- The goal is to create a scalar function that calculates an employee's bonus based on their performance rating and salary.
- CREATE FUNCTION : Defines the scalar function.
- IF...ELSE : Implements conditional logic to determine the bonus.
- RETURN : Computes and returns the bonus amount.
- Functions with conditional logic allow you to encapsulate complex business rules into reusable components.
- For example, in HR systems, you might use this function to calculate bonuses during annual appraisals.
1. Purpose of the Query :
2. Key Components :
3. Why use Conditional Logic in Functions? :
4. Real-World Application :
Additional Notes:
- Scalar functions are ideal for calculations that return a single value.
- Avoid overusing scalar functions in large queries, as they can degrade performance.
For more Practice: Solve these Related Problems:
- Write a SQL query to create a scalar function that calculates the commission earned by a salesperson based on their sales amount and commission rate.
- Write a SQL query to create a scalar function that determines whether a given year is a leap year.
- Write a SQL query to create a scalar function that computes the factorial of a given number.
- Write a SQL query to create a scalar function that evaluates the grade of a student based on their marks.
Go to:
PREV : Create a Trigger for Enforcing Business Rules.
NEXT : Create a Stored Procedure with Error Handling.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.