Back to: SQL Server Tutorial For Beginners and Professionals

Inline Table Valued Function in SQL Server

In our last article, we discussed how to create and call the scalar user-defined functions in SQL Server. In this article, we will discuss the Inline Table Valued Function in SQL Server with some examples.

From our previous article, we learned that a scalar user-defined function returns a single (scalar) value. On the other hand, an Inline Table Valued function, return a table.

What is a table-valued function?

In the case of Table-valued Function, we can return a table as an output from the function. These are again of two types.

1. Inline Table-valued Function
2. Multi-statement table value function

What are Inline table-valued functions in SQL Server?

In case of Inline Table Valued Function, the body of the function will have only a single select statement prepared with “RETURN” statement.

Points to Remember:

1. We specify Table as the return type instead of any scalar data type.
2. The function body is not closed between BEGIN and END block.’
3. The structure of the table that gets returned is determined by the select statement within the function.

Let us understand the Inline Table Valued Function in SQL Server with some examples.

Please use below SQL Script to create and populate the necessary database table with some test data which we are going to use.

-- Create Student Table
CREATE TABLE Student
(
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Gender VARCHAR(50),
  DOB DATETIME,
  Branch VARCHAR(50)
)

-- Populate the Student Table with test data
INSERT INTO Student VALUES(1, 'Pranaya', 'Male','1996-02-29 10:53:27.060', 'CSE')
INSERT INTO Student VALUES(2, 'Priyanka', 'Female','1995-05-25 10:53:27.060', 'CSE')
INSERT INTO Student VALUES(3, 'Anurag', 'Male','1995-04-19 10:53:27.060', 'ETC')
INSERT INTO Student VALUES(4, 'Preety', 'Female','1996-03-17 10:53:27.060', 'ETC')
INSERT INTO Student VALUES(5, 'Sambit', 'Male','1997-01-15 10:53:27.060', 'CSE')

SELECT * FROM Student

Example1: Create a function which accepts student id as input and returns that student details from the table.

CREATE FUNCTION FN_GetStudentDetailsByID
(
  @ID INT
)
RETURNS TABLE
AS
RETURN (SELECT * FROM Student WHERE ID = @ID)

-- Calling the Function:
SELECT * FROM FN_GetStudentDetailsByID(2)

OUTPUT:

Example2: Create a function to accept branch name as input and returns the list of students belongs to that branch.

CREATE FUNCTION FN_GetStudentDetailsByBranch
(
  @Branch VARCHAR(50)
)
RETURNS TABLE
AS
RETURN (SELECT * FROM Student WHERE Branch = @Branch)

Calling the Function:
SELECT * FROM FN_GetStudentDetailsByBranch('CSE')

OUTPUT:

NOTE: As the inline table-valued user-defined function, is returning a table, issue the select statement against the function, as if we are selecting the data from a TABLE.

Example3: Create a function that returns student Name, DOB and Branch by GENDER.

CREATE FUNCTION FN_GetStudentDetailsByGender
(
  @Gender VARCHAR(50)
)
RETURNS TABLE
AS
RETURN (SELECT Name, DOB, Branch FROM Student WHERE Gender = @Gender)

-- Calling the Function:
SELECT * FROM FN_GetStudentDetailsByGender('Male')

OUTPUT:

Where can we use Inline Table-valued Functions in SQL Server?

The Inline table-valued function in SQL Server can be used to achieve the functionalities of parameterized views, and the table returned by the inline table-valued function in SQL Server can also be used in joins with other tables.

Let us see an example for better understanding.

We are going to use the below Department and Employee tables to understand the need for Inline Table Valued Function in SQL Server.

Please use below SQL Script to create and populate the Department and Employee tables with some test data.

-- Create Department Table
CREATE TABLE Department
(
  ID INT PRIMARY KEY,
  DepartmentName VARCHAR(50)
)
GO

-- Populate the Department Table with test data
INSERT INTO Department VALUES(1, 'IT')
INSERT INTO Department VALUES(2, 'HR')
INSERT INTO Department VALUES(3, 'Sales')
GO

-- Create Employee Table
CREATE TABLE Employee
(
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Gender VARCHAR(50),
  DOB DATETIME,
  DeptID INT FOREIGN KEY REFERENCES Department(ID) 
)
GO

-- Populate the Employee Table with test data
INSERT INTO Employee VALUES(1, 'Pranaya', 'Male','1996-02-29 10:53:27.060', 1)
INSERT INTO Employee VALUES(2, 'Priyanka', 'Female','1995-05-25 10:53:27.060', 2)
INSERT INTO Employee VALUES(3, 'Anurag', 'Male','1995-04-19 10:53:27.060', 2)
INSERT INTO Employee VALUES(4, 'Preety', 'Female','1996-03-17 10:53:27.060', 3)
INSERT INTO Employee VALUES(5, 'Sambit', 'Male','1997-01-15 10:53:27.060', 1)
INSERT INTO Employee VALUES(6, 'Hina', 'Female','1995-07-12 10:53:27.060', 2)
GO

Lets first create an Inline Table Valued Function that returns the Employees by Gender from the Employees table.

CREATE FUNCTION FN_GetEmployeessByGender
(
  @Gender VARCHAR(50)
)
RETURNS TABLE
AS
RETURN (SELECT ID, Name, Gender, DOB, DeptID FROM Employee WHERE Gender = @Gender)

Now, let’s join the Employees returned by the inline table-valued function with the Departments table as shown below

SELECT Name, Gender, DOB, DepartmentName 
FROM FN_GetEmployeessByGender('Male') Emp
JOIN Department Dept on Dept.ID = Emp.DeptID

When we execute the above query, it will give us the below output.

In the next article, I will discuss the Multi-Statement Table Valued Function in SQL Server.

SUMMARY

In this article, I try to explain the Inline Table Valued Function in SQL Server step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.