Table-Valued Functions in SQL

Create an Inline TVF

Write an inline table-valued function that returns all orders placed by a given customer ID.

Expected output: A function that returns a table of orders filtered by the input customer ID.

Hint: Use a single SELECT statement with a WHERE clause filtering by customer ID.

Create a Multi-Statement TVF

Create a multi-statement TVF that returns products with inventory below a specified threshold and includes a calculated reorder quantity.

Expected output: A function returning products needing reorder with calculated reorder quantities.

Hint: Define a table variable, insert filtered rows, calculate reorder quantity, and return the table variable.

A Table-Valued Function is a user-defined function that returns a table data type, allowing it to be used like a table in SQL queries.

Inline TVFs consist of a single SELECT statement and are optimized by the SQL engine, while Multi-Statement TVFs can contain multiple statements, use table variables, and support procedural logic but may have performance overhead.

Use a Multi-Statement TVF when you need to perform complex processing involving multiple steps or procedural logic before returning the result set.