Cursor + Recursion Function

[Amr_Mohammad_R]

I need to know if there is a way to improve the below function as it takes long execution time

UserDefinedFunction [dbo].[GetChildrenAccount]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER 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
DECLARE @IsLeaf AS BIT
DECLARE @TotalValue AS DECIMAL(18, 3)

-- Openning Balance
IF @TypeTransaction = 0
SELECT @TotalValue = ISNULL(SUM(BalanceBeg), 0)
FROM (SELECT ((ISNULL(AccountBranch_LocalDebit, 0) -ISNULL(AccountBranch_LocalCredit, 0))/ CASE WHEN @Currnecy = 0 THEN 1 ELSE AccountBranch_CurrencyConv END) BalanceBeg
FROM AccountTree LEFT JOIN Account_InBranch 
ON (AccountBranch_AccountID = Account_ID)
WHERE (Account_ParentID = @AccountID OR Account_ID = @AccountID )
AND AccountBranch_BranchID = @Branch

UNION  ALL

SELECT  ISNULL(SUM(JournalDet_Debit),0) * CASE WHEN @Currnecy = 0 THEN AccountBranch_CurrencyConv ELSE 1 END BalanceBeg
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
GROUP BY AccountBranch_BegBalDebit , AccountBranch_BalanceDebit , AccountBranch_CurrencyConv
) TBALL

-- Total Debit
ELSE IF @TypeTransaction = 1
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

-- Total Credit
ELSE IF @TypeTransaction = 2
SELECT @TotalValue = ISNULL(SUM(JournalDet_Credit),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

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

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,@TypeTransaction,@Currnecy,@Branch)
    FETCH NEXT FROM GetAccount_ID INTO  @Account_ID     
    END               
    CLOSE GetAccount_ID      
DEALLOCATE GetAccount_ID    

RETURN ISNULL(@TotalValue,0)
END