Function That Returns a Table: Table Valued Functions

A function that returns a table works like a view that takes parameters. You write a query once, pass in a value, and use the result in a FROM clause like any table. This post builds one on a small product table. It also shows how the function differs from a view and from a function that returns one value.

Scalar and Table Valued

A scalar function returns a single value, such as a price with tax. A table valued function returns rows and columns. Both are called with parentheses, but you call them in different places. Use a scalar function in the SELECT list, and a table valued function after FROM.

CREATE FUNCTION dbo.WithTax (@Price DECIMAL(8,2))
RETURNS DECIMAL(8,2)
AS
BEGIN
  RETURN @Price * 1.08;
END;

SELECT dbo.WithTax(10.00) AS PriceWithTax;

The scalar call returns one value, 10.80.

The Sample Table

Products holds eight items in three categories. Run this in any test database. Every result below comes from SQL Server 2025.

CREATE TABLE Products (ProductID INT NOT NULL PRIMARY KEY, ProductName VARCHAR(30) NOT NULL, Category VARCHAR(20) NOT NULL, RetailPrice DECIMAL(8,2) NOT NULL);
GO

INSERT INTO Products (ProductID, ProductName, Category, RetailPrice) VALUES
  (1, 'Assam Tea', 'Tea', 9.50), (2, 'Green Tea', 'Tea', 8.25), (3, 'Chamomile', 'Tea', 7.00),
  (4, 'Cardamom', 'Spice', 4.75), (5, 'Cumin', 'Spice', 3.60), (6, 'Turmeric', 'Spice', 5.20),
  (7, 'Almonds', 'Snack', 11.00), (8, 'Roasted Chickpeas', 'Snack', 4.40);
GO

A Function With No Parameters

The body of a table valued function is a query. Put the query in parentheses after RETURN, and say RETURNS TABLE above it. This function returns the whole Products table.

CREATE FUNCTION dbo.GetAllProducts ()
RETURNS TABLE
AS
RETURN (SELECT ProductID, ProductName, Category, RetailPrice FROM Products);

Call it after FROM, with empty parentheses. The parentheses are required even when there is nothing inside. The call returns all eight rows.

SELECT * FROM dbo.GetAllProducts();

Add a Parameter

A function becomes useful when it takes a value. This one takes a category and returns only its products. The parameter appears in the WHERE clause.

CREATE FUNCTION dbo.GetCategoryProducts (@Category VARCHAR(20))
RETURNS TABLE
AS
RETURN (SELECT ProductID, ProductName, RetailPrice FROM Products WHERE Category = @Category);

Pass a category in the parentheses. Tea returns three products, and Spice returns three others.

SELECT * FROM dbo.GetCategoryProducts('Tea');

SELECT * FROM dbo.GetCategoryProducts('Spice');

Why Not a View?

A view returns a table, too, but its query is fixed. This view always shows Tea. To see Spice you would need a second view, and a third for Snack. The function covers all three categories with one definition.

CREATE VIEW dbo.vTeaProducts
AS
SELECT ProductID, ProductName, RetailPrice FROM Products WHERE Category = 'Tea';

Two Common Mistakes

Leave out the parameter, and SQL Server reports Msg 313: an insufficient number of arguments were supplied for the function. The message names the function, so the signature is easy to check.

SELECT * FROM dbo.GetCategoryProducts();

Leave out the parentheses, and you get Msg 216: parameters were not supplied for the function. The function name alone isn’t enough.

SELECT * FROM dbo.GetCategoryProducts;

Use It Like a Table

You can filter and sort the result as you would with a table. Here only the Tea products over 8.00 remain.

SELECT f.ProductName, f.RetailPrice
FROM dbo.GetCategoryProducts('Tea') AS f
WHERE f.RetailPrice > 8
ORDER BY f.ProductName;

The CROSS APPLY operator passes a value from each row of one query into the function. This query takes each category and asks the function for its products, then keeps the ones under 5.00.

SELECT c.Category, f.ProductName, f.RetailPrice
FROM (SELECT DISTINCT Category FROM Products) AS c
CROSS APPLY dbo.GetCategoryProducts(c.Category) AS f
WHERE f.RetailPrice < 5
ORDER BY c.Category, f.ProductName;

Inline and Multi-Statement

The functions above are inline, because the body is one query after RETURN. A multi-statement function declares a table variable, fills it between BEGIN and END, and returns it. The next function lists products at or under a price you pass in.

CREATE FUNCTION dbo.GetCheapProducts (@MaxPrice DECIMAL(8,2))
RETURNS @Result TABLE (ProductName VARCHAR(30), RetailPrice DECIMAL(8,2))
AS
BEGIN
  INSERT INTO @Result (ProductName, RetailPrice)
  SELECT ProductName, RetailPrice FROM Products WHERE RetailPrice <= @MaxPrice;
  RETURN;
END;

SELECT * FROM dbo.GetCheapProducts(5.00) ORDER BY ProductName;

The catalog view sys.objects tells the three kinds apart. It lists GetAllProducts and GetCategoryProducts as inline table valued functions. GetCheapProducts is a multi-statement table valued function, and WithTax is a scalar function.

SELECT name AS FunctionName, type_desc AS FunctionType FROM sys.objects WHERE type IN ('FN', 'IF', 'TF') ORDER BY name;

Choose an inline function when one query is enough. The posts linked at the end cover each kind in detail.

Clean Up

Remove the functions, the view and the table when you finish.

DROP FUNCTION dbo.GetCheapProducts;
DROP FUNCTION dbo.WithTax;
DROP VIEW dbo.vTeaProducts;
DROP FUNCTION dbo.GetCategoryProducts;
DROP FUNCTION dbo.GetAllProducts;
DROP TABLE Products;

What to Remember

A table valued function is a query with parameters that you call after FROM, always with parentheses. A view can’t take a value, and a scalar function returns only one. Pass a missing parameter or leave out the parentheses, and SQL Server tells you which rule you broke.

To go further, read Inline Table-Valued Functions: Parameterized Views That Stay Fast, Multi-Statement Table-Valued Functions and Interleaved Execution and SQL SERVER – How to Join a Table Valued Function with a Database Table.

A table valued function is not a stored result, it is a query waiting for a value.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.