Introduction

In this article, we will learn how to use the Table-Valued Function in SQL.

A Table-Valued Function returns a single rowset. You can use a Table-Valued Function anywhere in SQL that you can use a table because the return type of a Table-Valued Function is Table. You can also treat the Table-Valued Function just as you would a table.

• Create a Table-Valued Function

Syntax

CREATE FUNCTION function_name(@parameter data_type)
RETURNS TABLE
AS
RETURN
  SELECT STATEMENT

Please check How To Use Where Condition With Select Query In SQL to get a brief description of the SELECT STATEMENT.

Example

The subsequent statement would create a table-valued function named “udfStudentsAgeMoreThan” that selects all records from the “StudentInfo” table, where age is greater than the parameter (@age):

CREATE FUNCTION udfStudentsAgeMoreThan(@age int)
RETURNS TABLE
AS
RETURN
  SELECT ID, [Name]
  FROM StudentInfo
  WHERE Age > @age

• Alter a Table-Valued Function

Syntax

ALTER FUNCTION function_name(@parameter data_type)
RETURNS TABLE
AS
RETURN
  SELECT STATEMENT

Example

The subsequent statement would alter/update the existing table-valued function named “udfStudentsAgeMoreThan” that selects all records from the “StudentInfo” table, where age is greater than the parameter (@age) and the parameter is not NULL:

ALTER FUNCTION udfStudentsAgeMoreThan(@age int)
RETURNS TABLE
AS
RETURN
  SELECT ID, [Name]
  FROM StudentInfo
  WHERE Age > @age AND @age <> 0

• Execute a Table-Valued Function

Syntax

SELECT * FROM function_name(parameterValue);

Example

The subsequent statement would execute the table-valued function named “udfStudentsAgeMoreThan“.

SELECT * FROM udfStudentsAgeMoreThan(18);

Please give your valuable feedback and if you have any questions or issues about this article, please let me know.

Also, check How To Use Stored Procedure In SQL