sql server 2005 function calcul result error

Question

I create a udf in sql server 2005 return a table, i use this table in the udf for calculate the solde for client, but sometimes the result is correct and sometimes is not correct (the error value is in the Field "Solde" ) in the expression @Solde= @Solde + @Debit - @Credit

This is the code of the function :

CREATE FUNCTION  HistoryClient
(
    @IdClient      int
)
RETURNS  @Table_Var
    TABLE ( NAuto       BigInt Identity(1,1),
            Numero      nvarchar(20),
            Ligne       nvarchar(50), 
            IdClient    int, 
            Matricule   nvarchar(30), 
            DateBL      datetime, 
            Libelle     nvarchar(50), 
            Qte         decimal(18, 2), 
            PU          money, 
            Debit       money, 
            Credit      money,
            Solde       money
        ) 
AS
BEGIN

  Declare  @SoldeInitial    money

  DECLARE  @Debit      money
  DECLARE  @Credit     money
  DECLARE  @Solde      money
  DECLARE  @Solde1     money
  DECLARE  @Ligne      nvarchar(50)

  DECLARE History_Cursor CURSOR FOR 
    SELECT  Ligne, Debit, Credit, Solde
    FROM @Table_Var
    FOR UPDATE OF  Solde


  Select  @SoldeInitial = SoldeInitial
  From    Client
  Where   IdClient= @IdClient


  INSERT INTO @table_Var (Numero, Ligne, IdClient, Matricule, DateBL, Libelle, Qte, PU, Debit, Credit, Solde)
  Select    Numero, Ligne, IdClient, Matricule, DateBL, Libelle, Qte, PU, Debit, Credit, 0
  From  vwHistoryAllClients
  Where   IdClient= @IdClient
  Order By Ligne



    OPEN History_Cursor
    FETCH NEXT FROM  History_Cursor Into  @Ligne, @Debit, @Credit, @Solde1
    SET @Solde = @SoldeInitial

    WHILE @@FETCH_STATUS = 0
    BEGIN

       SET  @Solde= @Solde + @Debit - @Credit

       UPDATE  @Table_Var
       SET   Solde = @Solde
       WHERE CURRENT Of History_Cursor
       FETCH NEXT FROM  History_Cursor Into  @Ligne, @Debit, @Credit, @Solde1
    END
    CLOSE History_Cursor
    DEALLOCATE History_Cursor


 RETURN 
END

any solution

Is there an error or just wrong value? Does your data contain NULL values? — James Z, Apr 14 '15 at 11:56
the solde value is incorrect after n rows , in the beginning the solde value is correct and my data don't contain null values i have use isnull but some result — ghostdz, Apr 14 '15 at 14:09

score 0 · Answer 1 · edited May 23 '17 at 12:28

0

The SELECT statement in the definition of the CURSOR does not have an ORDER BY clause. This means that there is no guarantee as to the order in which the rows are returned from the cursor.

From what I gather, you are trying to calculate a running sum in the Solde column. However since you have no ORDER BY Ligne in the CURSOR definition, there is no guarantee that Solde will be the running sum following the Ligne order. Maybe that leads you to conclude that sometimes the result is correct and sometimes it isn't.

But alas, adding an ORDER BY clause to the CURSOR declaration makes it READ ONLY. See the following Stackoverflow thread as to what the reason is.

If in fact you need to calculate a running sum of (Credit-Debit) in the Solde column, please refer to the following Stackoverflow thread on calculating a running sum in SQL Server.

edited May 23 '17 at 12:28

Community

1
1

answered Apr 14 '15 at 12:38

TT.

15,774
6
47
88

when i add ORDER BY Ligne , i have message error in execution "cursor is read only" – ghostdz Apr 14 '15 at 14:03
@ghostdz Probably adding the ORDER BY clause makes the CURSOR read only. If in fact you intend to calculate a running sum of (Credit-Debit) in the Solde column, please refer to this [StackOverflow thread](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver). – TT. Apr 14 '15 at 14:44
Thanks, the problem was in the order, i declare Ligne as primary key this garantee the order of calcul of solde – ghostdz Apr 15 '15 at 09:08

sql server 2005 function calcul result error

1 Answers1