SQL Server Inline Table-Valued Functions (TVF)

 in SQL Server  tagged tvf by

Suppose you need a reusable table expression like a view, but you also need to be able to pass input parameters to the table expression. Views do not support input parameters. For this purpose SQL Server provides you with inline table-valued functions (TVFs).

Suppose you have a table that you want to query repeatedly. You want to specify a filter, such as a customer id and the number of rows to return in the result set. You want it sorted with the most recent at the top. Below is an example of this using a table called Sales.Orders from Itzik Ben-Gan’s database in the Microsoft Press book Querying T-SQL. 

SELECT TOP (3) orderid, orderdate, empid
FROM Sales.Orders WHERE custid = 1
ORDER BY orderdate DESC, orderid DESC

Below is Itzik’s code. 

-- Function GetTopOrders
IF OBJECT_ID(N'dbo.GetTopOrders', N'IF') IS NOT NULL DROP FUNCTION dbo.GetTopOrders;
GO
CREATE FUNCTION dbo.GetTopOrders(@custid AS INT, @n AS BIGINT) RETURNS TABLE
AS
RETURN
  SELECT TOP (@n) orderid, orderdate, empid
  FROM Sales.Orders
  WHERE custid = @custid
  ORDER BY orderdate DESC, orderid DESC;
GO

-- Test function
SELECT orderid, orderdate, empid
FROM dbo.GetTopOrders(1, 3) AS O;