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

Table-Valued Functions

  • 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

Footer Logo

Subscribe

Select Categories