Creating a Table-Valued Function in SQL

Create a Table-Valued Function

Write a SQL query to create a table-valued function that returns all employees in a specific department.

Solution:

-- Create a table-valued function to retrieve employees by department.
CREATE FUNCTION GetEmployeesByDepartmentTable
    (@DepartmentID INT) -- Input parameter for the department ID.
RETURNS TABLE -- Return type is a table.
AS
RETURN
    SELECT * FROM Employees WHERE DepartmentID = @DepartmentID;

Explanation:

1. Purpose of the Query :

1. The goal is to create a table-valued function that returns a set of rows based on a given department ID.

2. Key Components :

1. CREATE FUNCTION : Defines the table-valued function.
2. RETURNS TABLE : Specifies that the function returns a table.
3. RETURN : Provides the query to execute and return results.

3. Why Use Table-Valued Functions? :

1. Table-valued functions are useful for returning multiple rows and integrating seamlessly into queries.

4. Real-World Application :

1. For example, in reporting systems, you might use this function to filter employees by department dynamically.

Additional Notes:

• Table-valued functions are more flexible than scalar functions for returning multiple rows.
• Use this exercise to demonstrate how functions can simplify complex queries.

For more Practice: Solve these Related Problems:

• Write a SQL query to create a table-valued function that returns all products with a price greater than a specified value.
• Write a SQL query to create a table-valued function that retrieves all orders placed by a specific customer.
• Write a SQL query to create a table-valued function that lists all employees who joined after a certain date.
• Write a SQL query to create a table-valued function that finds all departments with more than a specified number of employees.

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

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