Understanding Table-Valued Functions in SQL Server: A Complete Guide

What is a table-valued function

A table-valued function is a user-defined function that returns data of a table type. The return type of a table-valued function is a table, therefore, you can use the table-valued function just like you would use a table.

Syntax for creating an inline table valued function

CREATE FUNCTION Function_Name(@Param1 DataType, @Param2 DataType..., @ParamN DataType)
RETURNS TABLE
AS
RETURN (Select_Statement)

If you look at the way we implemented this function, it is very similar to SCALAR function, with the following differences

• We specify TABLE as the return type, instead of any scalar data type
• The function body is not enclosed between BEGIN and END block. Inline table valued function body, cannot have BEGIN and END block.
• The structure of the table that gets returned, is determined by the SELECT statement with in the function.

Code: Use this below code to select a specific database

USE TestDatabase

Code: Create this below table to understand function

CREATE TABLE [dbo].[Emp_Master](
	[Emp_ID] [nchar](10) NULL,
	[Emp_Name] [nchar](10) NULL,
	[Emp_DOB] [date] NULL
) ON [PRIMARY]
 
 

Code: Create this below table

CREATE TABLE [dbo].[EmpSalary](
	[ID] [nchar](10) NULL,
	[Name] [nchar](10) NULL,
	[Salary] [numeric](10, 2) NULL,
	[Dept] [nchar](10) NULL
) ON [PRIMARY]

Insert some records inside above two table.

Code: use this code to see two table

Select * from EmpSalary
Select * from Emp_Master

ALTER TABLE Emp_Master
ADD Emp_DOB  DATE;

UPDATE Emp_Master SET Emp_DOB = '12-02-1996' WHERE Emp_ID = 1
UPDATE Emp_Master SET Emp_DOB = '11-02-1996' WHERE Emp_ID = 2
UPDATE Emp_Master SET Emp_DOB = '10-02-1996' WHERE Emp_ID = 3
UPDATE Emp_Master SET Emp_DOB = '09-02-1996' WHERE Emp_ID = 4
UPDATE Emp_Master SET Emp_DOB = '08-02-1996' WHERE Emp_ID = 5

Code: Create a simple function using this below code

/* User Defined Function */ 
-- Tabled Valued Function
CREATE FUNCTION SelectDataEmpSalary (
    @ID INT
)
RETURNS TABLE
AS
RETURN
    SELECT 
        Name,
        Salary,
        Dept
    FROM
        EmpSalary
    WHERE
        ID = @ID; 

-- Execute Function
SELECT * FROM SelectDataEmpSalary(1)

Where can we use Inline Table Valued functions

• Inline Table Valued functions can be used to achieve the functionality of parameterized views. We will talk about views, in view section.
• The table returned by the table valued function, can also be used in joins with other tables.