Create an inline table valued function

SQL >   SQL Functions >   Table Valued Functions  

401

---

Answer:

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

---

---

Join Our telegram group to ask Questions

Click below button to join our groups.