Creating a Scalar User-Defined Function in SQL

Create a User-Defined Function

Write a SQL query to create a scalar user-defined function that calculates an employee's annual salary.

Solution:

-- Create a function to calculate annual salary.
CREATE FUNCTION CalculateAnnualSalary
    (@MonthlySalary DECIMAL(10, 2)) -- Input parameter for monthly salary.
RETURNS DECIMAL(10, 2) -- Return type.
AS
BEGIN
    RETURN @MonthlySalary * 12; -- Calculate annual salary.
END;

Explanation:

1. Purpose of the Query :

1. The goal is to create a reusable function that calculates an employee's annual salary based on their monthly salary.

2. Key Components :

1. CREATE FUNCTION : Defines the user-defined function.
2. RETURNS : Specifies the return type of the function.
3. RETURN : Computes and returns the result.

3. Why use Functions? :

1. Functions allow you to encapsulate reusable logic and simplify complex calculations.

4. Real-World Application :

1. For example, in payroll systems, you might use this function to compute annual salaries for tax calculations.

Additional Notes:

• Scalar functions return a single value, while table-valued functions return a table.
• Avoid overusing scalar functions in large queries, as they can impact performance.

For more Practice: Solve these Related Problems:

• Write a SQL query to create a scalar function that calculates the total cost of an order including tax based on the order amount and tax rate.
• Write a SQL query to create a scalar function that determines the age of an employee based on their date of birth.
• Write a SQL query to create a scalar function that converts temperature from Celsius to Fahrenheit.
• Write a SQL query to create a scalar function that calculates the discount amount based on the total purchase amount and discount percentage.

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

Previous SQL Exercise: Create a Stored Procedure with Parameters.
Next SQL Exercise: Create a Trigger for Logging Changes.