title: How to use table valued functions in SQL Server
body: <p>I've a stored procedure here </p>
  
  <pre><code>ALTER PROCEDURE [dbo].[SortedReport]
  (
      @ClientID INT,
      @RecordLimit,
      @FromDate DATETIME,
      @ToDate DATETIME,
      @OrderBy NVARCHAR(MAX)
  )
  AS
  BEGIN
  SELECT TOP (@RecordLimit) 
          sv.ClientID,
          sv.VendorID,
          sv.ProductID,
          sv.TransactionTime,
          sv.ClientName,
          sv.VendorName,
          sv.ProductName,
          sv.ProductCode,
          sv.VendorCode,
          StockCount = dbo.GetStockCount(sv.ProductID, sv.VendorID, @ClientID, @FromDate, @ToDate),
          TransactionCount = dbo.GetTransCount(sv.ProductID, sv.VendorID, @FromDate, @ToDate),
          ProductCount     = dbo.GetProductCount(sv.ProductID, sv.VendorID),
  FROM SortedReportiew AS sv 
  WHERE (sv.ClientID = @ClientID)
      AND (sv.TransactionTime &gt;= @FromDate)
      AND (sv.TransactionTime &lt; @Date)
  </code></pre>
  
  <p>The function calls in the SELECT part is highly inefficient when I have large data. There function calls for 100 rows will mean a lot of reads.</p>
  
  <p>I need to remove the calls from there and have bulk read in a table valued function and to pass most report parameters to these functions</p>
  
  <pre><code>SELECT 
      sd.StockCount,
      sd.TransactionCount 
      pd.ProductCount,
      TransactionTime,
      ClientName,
      VendorName,
      ProductName,
      ProductCode,
      VendorCode
  FROM 
  ( 
      SELECT TOP (@RecordLimit) 
          sv.ClientID,
          sv.VendorID,
          sv.ProductID,
          sv.TransactionTime,
          sv.ClientName,
          sv.VendorName,
          sv.ProductName,
          sv.ProductCode,
          sv.VendorCode
      FROM SortedReportiew AS sv 
          --Begin Where {
      WHERE 
          (ClientID = @ClientID) 
          AND TransactionTime &gt;= @FromDate 
          AND TransactionTime &lt; @ToDate 
          --End Where }
  ) AS mainQuery 
  FULL JOIN GetStockDetails( 
       @ClientID,
       @FromDate,
       @ToDate, 
       ) AS sd 
  ON mainQuery.ClientID = sd.ClientID
  LEFT OUTER JOIN GetProductDetails( 
       @ProductID 
       @FromDate,
       @ToDate,
       ) AS pd 
  ON mainQuery.ClientID = pd.ClientID
  </code></pre>
  
  <p>I've n't tried table valued function before. What I h've written so far is</p>
  
  <pre><code>ALTER FUNCTION [dbo].[GetStockDetails] 
  (
     @ClientID INT
     @FromDate DATETIME,
     @ToDate DATETIME,
  )
  RETURNS @tblStockDetails TABLE (
    -- Columns returned by the function
          StockCount INT,
          TransactionCount  INT
  )
  AS
  
  BEGIN
  
  INSERT INTO @tblStockDetails
  
  SELECT 
      StockCount,
      TransactionCount
  FROM
  (
  SELECT 
      StockCount = (SELECT COUNT(*)... //  My Query here
      ....
  RETURN
  
  END
  </code></pre>
  
  <p>Can somebody advice on the format of the table valued function for this requirement.</p>