Hello,
I have this Store Procedure and i am new in "Cursor" programming

The store Procedure is this

CREATE PROCEDURE Testing_Cursor AS
DECLARE @AccCode varchar(12)
DECLARE @YEAR varchar(4)
Declare CODE CURSOR
FOR
SELECT DISTINCT(NLAcc_Code)
FROM NLMovement_TMP
OPEN CODE
BEGIN
FETCH NEXT FROM  CODE INTO @AccCode
WHILE @@FETCH_STATUS = 0
  BEGIN
   DECLARE CYEAR CURSOR
    FOR SELECT DISTINCT(AccYear) FROM  NLMOVEMENT_TMP WHERE
NLACC_CODE=@ACCCODE
    OPEN CYEAR
    FETCH NEXT FROM CYEAR INTO @YEAR
    WHILE @@FETCH_STATUS = 0
      BEGIN
    SELECT SUM(MOVEMENT)
    FROM NLMOVEMENT_TMP
    WHERE NLACC_CODE=@ACCCODE
    AND ACCYEAR=@YEAR
      END
    CLOSE CYEAR
END
END
CLOSE CODE
GO
I have an endless lopp in the second WHILE @@FETCH_STATUS = 0
it avtually never goes into the begin - end code after .
Am i using the WHILE statment correct??
Can anyone find the what is wrong with it??

Thanks
Savvas

The first thing you should learn about cursors is that you don't need
them! At least 99.99%, of the time cursors are not a good solution to
problems in SQL. Try this instead

SELECT SUM(movement)
FROM nlmovement_tmp
GROUP BY nlacc_code, accyear ;

If that's not what you wanted then please post DDL and sample data so
that we can understand your requireemnts better.

I would suggest you don't bother learning about cursors until you a
much more expert with "proper" SQL. Only then will you have the
knowledge to judge the exceptional situations when cursors make sense.

Hope this helps.

Hi Sawas,

        AND ACCYEAR=@YEAR

      FETCH NEXT FROM  CODE INTO @AccCode ---missed that here
      END
    CLOSE CYEAR
END
END
CLOSE CODE
GO


But what about NOT using a cursor, they can be slow (not to say that
they are slow, because I don´t want to start a religious war in here),
but If you are onyl doing queries, you should rely on a non cursor
solution:

SELECT SUM(MOVEMENT)
        FROM NLMOVEMENT_TMP
GROUP BY NLACC_CODE,ACCYEAR


HTH, jens Suessmeyer.

Well Actually I have this situation
I have a table with
AccCode,
AccMovement,
AccYear, and
AccPeriod

and my data looks like this
AccCode   AccYear  AccPeriod sumaryOfThe movement
110401    1998    02    541870.00
110401    1998    03    1210000.00
110401    1998    04    2687330.00
110401    1998    05    1220450.00
110401    1998    06    3508000.00
110401    1998    07    5606620.00
110401    1998    08    1567080.00
110401    1998    09    150000.00
110401    1998    10    1202180.00
110401    1998    11    1373980.00
110401    1998    12    2604600.00

And this account has data for many years ,
Now i want to make a table like this
accCode , AccYear , AccPeriod, Opening balance , movement ,
OpeningBalance+Movement where opening balance = closing balnce of
previus period

how can i do this ?

This would be a bit easier if you used proper dates. Put your financial
periods in a Calendar table instead.

I'm just guessing at the following table structure because you didn't
post it. Note the key.

CREATE TABLE nlmovement (acccode INTEGER NOT NULL, accyear INTEGER NOT
NULL /* Why not a DATETIME column? */ CHECK (accyear BETWEEN 1900 AND
2100), accperiod INTEGER NOT NULL CHECK (accperiod BETWEEN 1 AND 12),
amount NUMERIC(10,2), PRIMARY KEY (acccode,accyear,accperiod)) ;

Try this:

SELECT C.acccode, C.accyear, C.accperiod, C.amount,
SUM(P.amount)-C.amount AS opening_balance,
C.amount,
SUM(P.amount) AS closing_balance
FROM nlmovement AS C, nlmovement AS P
WHERE C.acccode = P.acccode
  AND (C.accyear > P.accyear
       OR (C.accyear = P.accyear
         AND C.accperiod >= P.accperiod))
GROUP BY C.acccode, C.accyear, C.accperiod, C.amount ;

In general it's unwise to store calculations in the database because
any kind of redundancy can lead to inconsistencies and incorrect
results. Don't add the balance columns to a table - just calculate them
in your queries and reports (in fact a reporting tool will probably
calculate the running balance more efficiently than my query will.)

Hope this helps.

INSERT table (column1, column2) (@variable, '100')
Simple JOIN turned ugly - Help!
Is the SQL Server Service Running?
Grouping,Paging Logic
sORTING ACROSS MULTIPLE COLUMN