How To Create A Table Valued Function In SQL Server

As an SQL database developer, I found that table-valued functions in SQL Server help us manage and manipulate data. In this article, I’ll walk you through the process of creating table-valued functions, their benefits, and some real-world examples you can apply to your database applications.

A table-valued function in SQL Server is a user-defined function that returns the result set as a table. It can return multiple rows and columns.

Table-valued functions are of two types.

1. Inline Table-Valued Functions (iTVFs)
2. Multi-Statement Table-Valued Functions (MSTVFs)

Creating Inline Table-Valued Functions

Let us execute the SQL script below.

CREATE FUNCTION dbo.GetProductByBrand
(
    @ProductBrand NVARCHAR(50)
)
RETURNS TABLE
AS
RETURN
(
    SELECT ProductID, ProductName, ProductBrand
    FROM Product
    WHERE ProductBrand = @ProductBrand
)

Check out How To Find Column Name In Table SQL Server

After executing the above script, it was executed successfully, as shown in the screenshot below.

Now, let us call the function using the SQL query below.

SELECT * FROM dbo.GetProductByBrand('Honda')

After executing the above statement, I got the expected output, as shown in the screenshot below.

Check out How To Check If Column Exists In SQL Server Database

Creating Multi-Statement Table-Valued Functions

Let’s execute the script below.

CREATE FUNCTION dbo.GetProductByName
(
    @ProductName NVARCHAR(50),
    @TopN INT
)
RETURNS @Results TABLE
(
    ProductID INT,
    ProductName NVARCHAR(60),
    TotalPurchases DECIMAL(18,2)
)
AS
BEGIN
    INSERT INTO @Results
    SELECT TOP (@TopN)
        P.productID,
        p.productName,
        SUM(o.TotalAmount) AS TotalPurchases
    FROM Product p
    JOIN Orders o ON p.ProductID = o.ProductID
    WHERE p.ProductName = @ProductName
    GROUP BY p.ProductID, P.ProductName
    ORDER BY TotalPurchases DESC

    RETURN
END

The query was executed successfully. Check out the screenshot below for your reference.

Check out How To Create A Table Variable In SQL Server

Let’s execute the below SQL select statement to call the above function.

SELECT * FROM dbo.GetProductByName('BRV', 5)

After executing the above query, I got the expected output, as shown in the screenshot below.

Conclusion

Table-valued functions in SQL Server are powerful functions that help boost your database development activities and write more efficient code. Now, start implementing the simple inline functions, and then, based on your requirements, you can move to the Multi-Statement Table-Valued Functions for the complex scenarios based on the information mentioned in this article.

You may also like following the articles below.

• How to get table size in SQL Server
• How To Create A Table Variable In SQL Server
• Create table if not exists SQL Server
• How To Change Column Name In SQL Server