Why is ORDER BY allowed in TVF but not in INLINE TVF?

Joe,

Trouble with VIEWs that they don't take parameters.

Consider INLINE TABLE-VALUED function as a view with parameters:

/******** INLINE table-valued user-defined function ***********/
CREATE FUNCTION  ufnSalesByCountry (@CountryTerritory varchar(60))
RETURNS TABLE AS
RETURN 
SELECT TOP 1000 [SalesPersonID]
      ,[FullName]
      ,[JobTitle]
      ,[SalesTerritory]
      ,[2006]
      ,[2007]
      ,[2008]
  FROM [AdventureWorks2012].[Sales].[vSalesPersonSalesByFiscalYears]
  WHERE SalesTerritory = @CountryTerritory;
GO

SELECT * FROM ufnSalesByCountry('Germany');
GO
/*
SalesPersonID   FullName        JobTitle        SalesTerritory  2006    2007    2008
288     Rachel B Valdez Sales Representative    Germany NULL    NULL    1827066.7118
*/

---

Kalman Toth Database & OLAP Architect sqlusa.com

New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012