title: Multi-statement Table Valued Function vs Inline Table Valued Function
body: <p>A few examples to show, just incase:</p>
  
  <p><em>Inline Table Valued</em></p>
  
  <pre><code>CREATE FUNCTION MyNS.GetUnshippedOrders()
  RETURNS TABLE
  AS 
  RETURN SELECT a.SaleId, a.CustomerID, b.Qty
      FROM Sales.Sales a INNER JOIN Sales.SaleDetail b
          ON a.SaleId = b.SaleId
          INNER JOIN Production.Product c ON b.ProductID = c.ProductID
      WHERE a.ShipDate IS NULL
  GO
  </code></pre>
  
  <p><em>Multi Statement Table Valued</em></p>
  
  <pre><code>CREATE FUNCTION MyNS.GetLastShipped(@CustomerID INT)
  RETURNS @CustomerOrder TABLE
  (SaleOrderID    INT         NOT NULL,
  CustomerID      INT         NOT NULL,
  OrderDate       DATETIME    NOT NULL,
  OrderQty        INT         NOT NULL)
  AS
  BEGIN
      DECLARE @MaxDate DATETIME
  
      SELECT @MaxDate = MAX(OrderDate)
      FROM Sales.SalesOrderHeader
      WHERE CustomerID = @CustomerID
  
      INSERT @CustomerOrder
      SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty
      FROM Sales.SalesOrderHeader a INNER JOIN Sales.SalesOrderHeader b
          ON a.SalesOrderID = b.SalesOrderID
          INNER JOIN Production.Product c ON b.ProductID = c.ProductID
      WHERE a.OrderDate = @MaxDate
          AND a.CustomerID = @CustomerID
      RETURN
  END
  GO
  </code></pre>
  
  <p>Is there an advantage to using one type (in-line or multi statement) over the other? Is there certain scenarios when one is better than the other or are the differences purely syntactical? I realise the two example queries are doing different things but is there a reason I would write them in that way?</p>
  
  <p>Reading about them and the advantages/differences haven't really been explained.</p>