A table-valued function in SQL Server is a user-defined function that returns a table as its output. This type of function can be very useful when you need to perform complex data transformations or calculations and return the results as a table that can be used in subsequent SQL statements.

There are two types of table-valued functions in SQL Server: inline table-valued functions and multi-statement table-valued functions.

Inline table-valued function

An inline table-valued function is a function that is defined using a single SELECT statement. It returns a table that is derived from the SELECT statement. Here's an example of an inline table-valued function that returns a table of customer orders:

CREATE FUNCTION GetCustomerOrders (@CustomerID INT)
RETURNS TABLE
AS
RETURN
(
SELECT OrderID, OrderDate, TotalAmount
FROM Orders
WHERE CustomerID = @CustomerID
);

To use this function, you can simply call it in a SELECT statement, like this:

SELECT *
FROM GetCustomerOrders(1234);

Multi-statement table-valued function

A multi-statement table-valued function is a function that is defined using multiple statements, including variable declarations, control-of-flow statements, and SQL statements. It returns a table that is generated by a SELECT statement at the end of the function. Here's an example of a multi-statement table-valued function that returns a table of product sales by month:

CREATE FUNCTION GetProductSalesByMonth (@ProductID INT)
RETURNS @SalesByMonth TABLE
(
MonthOfYear INT,
SalesAmount MONEY
)
AS
BEGIN
DECLARE @StartDate DATE = '2022-01-01';
DECLARE @EndDate DATE = '2022-12-31';
DECLARE @CurrentMonth DATE = @StartDate;
WHILE @CurrentMonth <= @EndDate
BEGIN
INSERT INTO @SalesByMonth
SELECT MONTH(OrderDate), SUM(TotalAmount)
FROM Orders
WHERE ProductID = @ProductID
AND MONTH(OrderDate) = MONTH(@CurrentMonth)
AND YEAR(OrderDate) = YEAR(@CurrentMonth)
GROUP BY MONTH(OrderDate)
SET @CurrentMonth = DATEADD(MONTH, 1, @CurrentMonth);
END
RETURN;
END;

To use this function, you can call it in a SELECT statement and join the resulting table with other tables, like this:

SELECT *
FROM Products
INNER JOIN GetProductSalesByMonth(123) AS SalesByMonth
ON Products.ProductID = SalesByMonth.ProductID;

Table-valued functions can be very powerful and flexible tools for working with complex data transformations and calculations in SQL Server. They can simplify your code and make it easier to reuse common data queries throughout your application.