Inline Table Valued Function in SQL Server

In SQL Server, an Inline Table-valued function (ITVF) is a user-defined function that returns a table as its result. Unlike a scalar function that returns a single scalar value, an Inline Table Valued Function returns a result set that can be used in a query just like a table. An Inline Table Valued Function is “inline” because its definition is part of a query, rather than being a standalone object.

An ITVF is defined using the CREATE FUNCTION statement with the RETURNS TABLE clause. The function can have one or more input parameters, and it must use a RETURN statement to return a table variable that defines the structure of the result set.

Syntax:

Creating  Function :

CREATE FUNCTION [schema_name.]function_

name([@parameter_name] [data_type])

RETURNS TABLE

AS

RETURN

(

   SELECT column1, column2, …

   FROM table_name

   WHERE condition

)

Update/Alter Function:

ALTER FUNCTION [schema_name.]function

_name([@parameter_name] [data_type])

RETURNS TABLE

AS

RETURN

(

   SELECT column1, column2, …

   FROM table_name

   WHERE condition

)

Delete Function:

DELETE FUNCTION Function_Name

Step 1: Now take an example, Here we have a table Student which has Id,Name and Marks Columns .

-- create the Student table
CREATE TABLE Student
 (
   Id INT IDENTITY(1,1) PRIMARY KEY,
   Name VARCHAR(50) NOT NULL,
   Marks INT NOT NULL
)
-- insert  records
INSERT INTO Student (Name, Marks)
VALUES ('Tushar', 60), ('Kunal', 80), ('Shivam', 30), 
('Rushi', 45),('Mahesh',60),('Shubham',39),('Rahul',97)

select * from Student

Output:

 

Step 2: Then Create Function ‘GetNamesbyMarks‘ which takes input parameter @mark and give the table as the output.

 

Step 3: Then pass the value for input parameter @mark  as 40. 

 

 

Step 4: Hence we get the Name of the students who got marks greater than 40 . The result set is returned as a table that can be used in the rest of the query.

Inline Table Valued Function can be used to encapsulate complex queries and make them easier to use and reuse in different parts of your code. By returning a result set that can be used just like a table, an Inline Function can simplify your SQL code and make it more modular and maintainable.