views:

150

answers:

1

This is related to the following post:

The function that retrieves the item counts for each folder (category) is:

ALTER FUNCTION [dbo].[GetFolderReceiptCount]
(
    -- Add the parameters for the function here
    @FolderID bigint
)
RETURNS int
AS
BEGIN

    DECLARE @Return int

    SET @Return = 0

    SELECT 
     --H.ReceiptFolderID, 
     @Return = COUNT(H.ReceiptFolderID)
    FROM
     tbl_ReceiptFolderLnk H
     JOIN tbl_Receipt D ON H.ReceiptID = D.ReceiptID
    WHERE ReceiptFolderID=@FolderID
    GROUP BY
     H.ReceiptFolderID

    -- Return the result of the function
    RETURN @Return  

END

How can this be altered to return counts for each parent?

+1  A: 

You should change the funtion to return a table variable, or use a stored procedure so that you can get a data set

The SQL Statement should be similar to this:

SELECT 
    H.ReceiptFolderID, COUNT(H.ReceiptFolderID)
FROM
    tbl_ReceiptFolderLnk H
    JOIN tbl_Receipt D ON H.ReceiptID = D.ReceiptID
WHERE ReceiptFolderID=@FolderID
GROUP BY
    H.ReceiptFolderID
Raj More
This is basically the same thing I had above. Because this is a function I just need to return the count. I need to traverse the current and child folder ID's (categories) to get the total count of items, not just in the current, but in the current and its children.
ElHaix