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:

1. Purpose of the Query :

1. The goal is to create a scalar function that calculates an employee's bonus based on their performance rating and salary.

2. Key Components :

1. CREATE FUNCTION : Defines the scalar function.
2. IF...ELSE : Implements conditional logic to determine the bonus.
3. RETURN : Computes and returns the bonus amount.

3. Why use Conditional Logic in Functions? :

1. Functions with conditional logic allow you to encapsulate complex business rules into reusable components.

4. Real-World Application :

1. For example, in HR systems, you might use this function to calculate bonuses during annual appraisals.

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.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Create a Trigger for Enforcing Business Rules.
Next SQL Exercise: Create a Stored Procedure with Error Handling.