SQL Server APPLY with Table-Valued Function
SQL Server APPLY with Table-Valued Function is a SQL clause in the SQL Server Specific category. SQL Server APPLY invokes a table-valued function for each row. CROSS = matching only, OUTER = all rows. The syntax is SELECT * FROM t1 CROSS APPLY dbo.fn_GetData(t1.id);. It returns applied function result. A typical example: -- Create a table-valued function: CREATE FUNCTION dbo.GetRecentOrders(@cust_id INT) RETURNS TABLE AS RETURN ( SELECT TOP 3 id, order_date, amount FROM orders WHERE customer_id = @cust_id ORDER BY order_date DESC ); -- Use with CROSS APPLY: SELECT c.name, r.id AS order_id, r.amount FROM customers c CROSS APPLY dbo.GetRecentOrders(c.id) r; -- Only customers with orders -- Use with OUTER APPLY: SELECT c.name, r.id AS order_id, r.amount FROM customers c OUTER APPLY… A close relative is OUTPUT Clause, which sQL Server: returns values from DML statements (like PostgreSQL RETURNING). A close relative is STUFF(), which sQL Server: deletes a specified length of characters from a string and inserts another string at the start position.