How to use table valued functions in SQL Server

Question

I've a stored procedure here

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 >= @FromDate)
    AND (sv.TransactionTime < @Date)

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.

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

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 >= @FromDate 
        AND TransactionTime < @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

I've n't tried table valued function before. What I h've written so far is

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

Can somebody advice on the format of the table valued function for this requirement.

Do you plan to use the function in other SPs too? Otherwise I would use a temp table or a declared table to gain the ability to use primary key. The table-valued-function will be slow to join if there are alot of data. — Johan
– Johan, Commented May 17, 2011 at 9:10

Johan · Accepted Answer · 2011-05-17 12:07:01Z

4

Answer to the comment above.

Well, you should make the temp table so big that you have all entries (rows) that isnt constants. And limit the table with the constants i.e. @fromDate and @toDate

For instance:

dbo.GetStockCount(sv.ProductID, sv.VendorID, @ClientID, @FromDate, @ToDate)

This should be done something like this:

INSERT INTO #tempTable
SELECT  theValuePreviouslyFetchedFromFunction
        ,ProductID
        ,VendorID
FROM    yourTable
WHERE   clientIDValue = @ClientID 
    AND dateValue <= @fromDate
    AND dateValue > @toDate

And you define the temp table with primary key on productID and vendorID.

Then you can join this table with the initial one on these two columns and have the result in the select.

Without any more example code it is hard to help you group all functions to one query.

Hope this helps.

answered May 17, 2011 at 12:07

Johan

1,1927 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use table valued functions in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related