Introduction:

Here we are going to explain what is Table-valued functions in SQL or how to create Table-valued functions in SQL  or how to use Table-valued functions in SQL server with an example.

Description:

Scalar-valued functions return a single value. Table-valued functions return tabular result sets (“tabular” meaning like a table). Basically, a Table-Valued Function is a function that returns a table (a set of rows). Table-valued functions can be based on one or more base tables.

Creating and Implementing Table-Valued Functions

CREATE FUNCTION Fn_Getcustomer(@id int)
RETURNS @TRACKCUSTOMER TABLE (
   Id int NOT NULL,
   NAME VARCHAR(120) NULL,
   ADDRESS1 VARCHAR(250) NULL
) 
AS
BEGIN
   INSERT INTO @TRACKCUSTOMER (Id, NAME, ADDRESS1)
   SELECT ID, NAME, ADDRESS1 FROM CUSTOMER WHERE ID= @id; 
   RETURN;
END;

Using Table-Valued Functions

SELECT * FROM Fn_Getcustomer(2)

In above example, I have created a Table-valued function that returns customer details in tabular format (a set of rows). It takes an input parameter (ID) and returns customer details.