Change cursor to common table

Amr Mohammad

How can I change the below code snippet from using cursor to using table expression. The below code is about getting specific data for general account and all accounts below such given account

FUNCTION [dbo].[GetChildrenAccount]
(@AccountID INT,
@DateFrom DATETIME,
@DateTo DATETIME,
--@TypeTransaction INT,
--@Currnecy INT,
@Branch INT)

RETURNS DECIMAL(18,3)
AS
BEGIN

DECLARE @Account_ID AS BIGINT,
@IsLeaf AS BIT,
@TotalValue AS DECIMAL(18,3),

SELECT @TotalValue = ISNULL(SUM(JournalDet_Debit),0) * CASE WHEN @Currnecy = 0 THEN AccountBranch_CurrencyConv ELSE 1 END
FROM AccountTree
INNER JOIN Account_InBranch ON (AccountBranch_AccountID = Account_ID)
LEFT JOIN Journal_Details ON (Account_ID = JournalDet_AccountID)
LEFT JOIN Journal_Head ON (Journal_ID = JournalDet_HeadID)
WHERE (Account_ParentID = @AccountID OR Account_ID = @AccountID )
AND Journal_BranchID = @Branch
AND Journal_Date >= @DateFrom AND Journal_Date <= @DateTo
GROUP BY AccountBranch_BegBalDebit , AccountBranch_BalanceDebit , AccountBranch_CurrencyConv

OPEN GetAccount_ID
FETCH NEXT FROM GetAccount_ID INTO @Account_ID
WHILE @@fetch_Status = 0
BEGIN
SET @TotalValue = @TotalValue + dbo.GetChildrenAccount(@Account_ID,@DateFrom,@DateTo,@Branch)
FETCH NEXT FROM GetAccount_ID INTO @Account_ID
END
CLOSE GetAccount_ID
DEALLOCATE GetAccount_ID

RETURN ISNULL(@TotalValue,0)
END

Richard Deeming

You seem to be missing the definition of your GetAccount_ID cursor.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Amr Mohammad

I removed some parts of the function when posting may be I forgot and remove the declaration for

GetAccount_ID

and here is the declaration

DECLARE GetAccount_ID CURSOR STATIC FOR
SELECT Account_ID FROM AccountTree WHERE Account_ParentID = @AccountID AND Account_Isleaf = 0

Richard Deeming

It's tricky to answer without your table definitions and some sample data, but this should get you close:

WITH cteAccountTree As
(
SELECT
@AccountID As Account_ID

UNION ALL

SELECT
    P.Account\_ID
FROM
    cteAccountTree As P
    INNER JOIN AccountTree As C
    ON C.Account\_ParentID = P.Account\_ID
And
    Account\_Isleaf = 0

)
SELECT
@TotalValue = IsNull(Sum(JournalDet_Debit), 0) * CASE WHEN @Currency = 0 THEN AccountBranch_CurrencyConv ELSE 1 END
FROM
cteAccountTree As T
INNER JOIN Account_InBranch As B ON B.AccountBranch_AccountID = T.Account_ID
LEFT JOIN Journal_Details As JD ON JD.JournalDet_AccountID = T.Account_ID
LEFT JOIN Journal_Head As JH ON JH.Journal_ID = JD.JournalDet_HeadID
WHERE
Journal_BranchID = @Branch
And
Journal_Date Between @DateFrom And @DateTo
;

The first part is a recursive common table expression (CTE): How to use recursive CTE calls in T-SQL[^] This should return the list of all accounts in the tree which have the specified account ID as an ancestor, excluding any with the Account_Isleaf flag set. NB: If your tree is particularly deep, you might run into the default recursion limit. There will probably be a way to work around it, but it won't be as nice as the recursive CTE solution. :) You then join the tree of account IDs to your branch and journal tables to calculate the total in one hit. The only part I'm not sure about: your code seems to be double-counting at each level:

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
** - Homer**

Amr Mohammad

Thank very much for help. I'm trying to learn about recursive expression table however I cannot know how its really work :(. The Where clause you mentioned at the end of your post to be able to get the parent account and its child(s) too and that what I forgot to mention in the post because I did not know it at the time when I wrote the post what I need to get is the parent account and its child(s) account(s) and if those child(s) account(s) is/are a parent(s) for another child(s) account(s) I got them too :) it seems stupid but this function is used within a procedure to get the balance of the account chart within the system. Finally, I'm thanking you too much. could you help sending me a link to an explanation of the recursive CTE and how it is implemented

Richard Deeming

I still think the query you posted is double-counting:

You get the total for account A;
You then loop through the child accounts:
- You get the total for account A.1 and its parent account (account A), and add it to the total;
- You get the total for account A.2 and its parent account (account A), and add it to the total;
Etc.

By the time you've finished, you've added the total for account A multiple times - once for each child account. From your description, you just want to get the total balance for an account and its descendants, which means that your original query isn't correct. My previous answer had a link to an article explaining recursive CTEs: How to use recursive CTE calls in T-SQL[^] Google also returns the documentation on TechNet: http://technet.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx[^] And a blog post: http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Amr Mohammad

Appreciating your help. Thanks very much I will review the function again as I'm not the one who wrote it however I'm trying to optimize a Stored Procedure performance which selects from a lot of tables + calling this function. the last time I executed the procedure it nearly took over 14 minutes without a single result while it was still executing :(