NEED HELP ABOUT CURSOR

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
SavvasThe 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.
David Portas
SQL Server MVP
--|||Hi Sawas,
AND ACCYEAR=3D@.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=B4t 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.
David Portas
SQL Server MVP
--sql