In SQL Server, functions play a crucial role in encapsulating code and reducing code repetition. One type of function that is commonly used is the inline table-valued function (iTVF). In this article, we will explore the basics and common usage scenarios of iTVFs, and provide practical examples to consolidate your understanding.

Why Use Functions in SQL Server?

Functions in SQL Server allow us to wrap up code in a single executable database object. This promotes code reuse and reduces repetition. Additionally, using functions helps to declutter code and improve maintainability.

What are Inline Table-Valued Functions?

An inline table-valued function is a user-defined function that returns a table data type and can accept parameters. Unlike views, iTVFs allow parameterized usage, making them more flexible. They can be used in SELECT statements after the FROM clause, just like a table.

Creating an Inline Table-Valued Function

To create an iTVF, you need to define the function name, parameters, and the SELECT statement that will be the output of the function. Here’s an example:

CREATE FUNCTION [dbo].[udfGetProductList] (@SafetyStockLevel SMALLINT)
RETURNS TABLE AS
RETURN (
    SELECT Product.ProductID, Product.Name, Product.ProductNumber
    FROM Production.Product
    WHERE SafetyStockLevel >= @SafetyStockLevel
)

In the above example, we create an iTVF called “udfGetProductList” that accepts a parameter called “@SafetyStockLevel” of type SMALLINT. The function returns a table containing the ProductID, Name, and ProductNumber columns from the Product table, filtered by the SafetyStockLevel parameter.

Executing an Inline Table-Valued Function

To execute an iTVF, you can use a SELECT statement and specify the function name along with the parameter values. Here’s an example:

SELECT * FROM dbo.udfGetProductList(100)

In the above example, we execute the iTVF with a parameter value of 100. The function returns a result set based on this parameter value.

You can also apply additional filtering or join the iTVF with other tables in your queries. Here’s an example using a WHERE clause:

SELECT * FROM dbo.udfGetProductList(100) WHERE Name LIKE 'Chainring%'

In the above example, we filter the output of the iTVF to only include products with a name starting with “Chainring”.

You can also use the JOIN clause with an iTVF. Here’s an example:

SELECT PUdfList.ProductNumber, PUdfList.Name, PCost.StandardCost
FROM dbo.udfGetProductList(100) AS PUdfList
INNER JOIN Production.ProductCostHistory AS PCost ON PUdfList.ProductId = PCost.ProductID
WHERE PUdfList.ProductId = 717

In the above example, we join the ProductCostHistory table with the iTVF and include the StandardCost column from the ProductCostHistory table in the result set.

Usage of Default Parameters

iTVFs can have default parameter values, allowing you to execute the function without explicitly specifying a value for the parameter. Here’s an example:

ALTER FUNCTION [dbo].[udfGetProductList] (@SafetyStockLevel SMALLINT, @MFlag BIT = 0)
RETURNS TABLE AS
RETURN (
    SELECT Product.ProductID, Product.Name, Product.ProductNumber
    FROM Production.Product
    WHERE SafetyStockLevel >= @SafetyStockLevel AND MakeFlag = @MFlag
)

In the above example, we add a new parameter called “@MFlag” to the iTVF with a default value of 0. This means that if the parameter is not provided when executing the function, it will default to 0. Here’s how to execute the function with the default parameter:

SELECT * FROM dbo.udfGetProductList(100, DEFAULT)

Passing Multiple Parameters into an Inline Table-Valued Function

In some cases, you may need to pass multiple parameter values to an iTVF. To achieve this, you can create a user-defined table type and use table-valued parameters. Here’s an example:

CREATE TYPE ProductNumberList AS TABLE (ProductNum nvarchar(25))

ALTER FUNCTION [dbo].[udfGetProductList] (@SafetyStockLevel SMALLINT, @MFlag BIT = 0, @ProductList ProductNumberList READONLY)
RETURNS TABLE AS
RETURN (
    SELECT Product.ProductID, Product.Name, Product.ProductNumber
    FROM Production.Product
    WHERE SafetyStockLevel >= @SafetyStockLevel AND MakeFlag = @MFlag AND Product.ProductNumber IN (SELECT ProductNum FROM @ProductList)
)

DECLARE @TempProductList AS ProductNumberList
INSERT INTO @TempProductList VALUES ('EC-R098'), ('EC-T209')
SELECT * FROM dbo.udfGetProductList(100, 1, @TempProductList)

In the above example, we create a user-defined table type called “ProductNumberList” to hold multiple parameter values. We then modify the iTVF to accept this table-valued parameter using the READONLY statement. Finally, we declare a variable of type “ProductNumberList” and populate it with multiple parameter values before executing the function.

Conclusion

In this article, we have explored the basics of inline table-valued functions in SQL Server. We have learned how to create and execute iTVFs, use default parameters, and pass multiple parameters using table-valued parameters. By leveraging iTVFs, you can make your database development process more modular and efficient, while avoiding code repetition.