Home / Questions / Create an inline table valued function
Explanatory Question

Create an inline table valued function

👁 425 Views
📘 Detailed Answer
🕒 Easy to Read
Read the answer carefully and go through the related questions on the right side to improve your understanding of this topic.

Answer with Explanation

Code: Create a function that returns EMPLOYEES by GENDER.



CREATE FUNCTION fn_EmployeesByGender(@Gender nvarchar(10))
RETURNS TABLE
AS
RETURN (Select Id, Name, DateOfBirth, Gender, DepartmentId
      from  Employees
      where Gender = @Gender)

Code: Calling the user defined function


Select * from fn_EmployeesByGender('Male')

Code: Joining the Employees returned by the function, with the Departments table



Select Name, Gender, DepartmentName 
from fn_EmployeesByGender('Male') E
Join tblDepartment D on D.Id = E.DepartmentId

Prerequisite Code: 


 CREATE TABLE Employees
(
Id INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
Name NVARCHAR(50),
DateOfBirth DATETIME,
Gender NVARCHAR(50),
DepartmentId INT
);

INSERT INTO Employees
VALUES ('Rambo', '1980-12-30', 'Male', 1)

INSERT INTO Employees
VALUES ('Roma', '1982-09-01 12:02:36.260', 'Female', 2)

INSERT INTO Employees
VALUES ('Inza', '1985-08-22 12:03:30.370', 'Male', 1)

INSERT INTO Employees
VALUES ('Sara', '1979-11-29 12:59:30.670', 'Female', 3)

INSERT INTO Employees
VALUES ('Azam', '1978-11-29 12:59:30.670', 'Male', 1)

Select * from Employees