Running total count in stored procedure

[Tracey]

in my procedure, I want to count the number of rows that have errored
during an insert statement - each row is evaluated using a cursor, so
I am processing one row at a time for the insert. My total count to
be displayed is inside the cursor, but after the last fetch is called.
Wouldn't this display the last count? The problem is that the count is
always 1. Can anyone help?

here is my code,

.... cursor fetch
begin ... cursor
if error then:
begin

INSERT INTO US_ACCT_ERRORS( ERROR_NUMBER, ERROR_DESC, cUSTOMERNUMBER,
CUSTOMERNAME, ADDRESS1, ADDRESS2, CITY,
STATE, POSTALCODE, CONTACT, PHONE, SALESREPCODE,
PRICELEVEL, TERMSCODE, DISCPERCENT, TAXCODE,
USERCOMMENT, CURRENCY, EMAILADDRESS, CUSTOMERGROUP,
CUSTINDICATOR, DT_LOADED)
VALUES(@ERRORNU M, @ERRORDESC,
@CUSTOMERNUMBER , @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY,
@STATE, @POSTALCODE, @CONTACT, @PHONE, @SALESREPCODE,
@PRICELEVEL, @TERMSCODE, @DISCPERCENT, @TAXCODE,
@USERCOMMENT, @CURRENCY, @EMAILADDRESS, @CUSTOMERGROUP,
@CUSTINDICATOR, @DTLOADED)

SET @ERRORCNT = @ERRORCNT + 1

END --error

--
FETCH NEXT FROM CERNO_US INTO
@CUSTOMERNUMBER , @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY, @STATE,
@POSTALCODE, @CONTACT, @PHONE, @SALESREPCODE, @PRICELEVEL, @TERMSCODE,
@DISCPERCENT, @TAXCODE, @USERCOMMENT, @CURRENCY,@EMAI LADDRESS,
@CUSTOMERGROUP, @CUSTINDICATOR, @DTLOADED
--
IF @ERRORCNT > 0
INSERT INTO PROCEDURE_RESUL TS(PROCEDURE_NA ME, TABLE_NAME, ROW_COUNT,
STATUS)
VALUES('LOAD_AC COUNTS', 'LOAD_ERNO_US_A CCT', @ERRORCNT, 'FAILED
INSERT/UPDATE')

END -- cursor
CLOSE CERNO_US
DEALLOCATE CERNO_US

[Erland Sommarskog]

Tracey (tr**********@i tsservices.com) writes:

in my procedure, I want to count the number of rows that have errored
during an insert statement - each row is evaluated using a cursor, so
I am processing one row at a time for the insert. My total count to
be displayed is inside the cursor, but after the last fetch is called.
Wouldn't this display the last count? The problem is that the count is
always 1. Can anyone help?

As I read your code, you insert a row into PROCEDURE_RESUL TS as soon as
@ERRORCNT is > 0, that is 1.

I don't know what you mean with "be displayed is inside the cursor, but
after the last fetch is called" - that makes no sense to me. You don't
really display the data what I can see, and you should just as well
insert into PROCEDURE_RESUL TS after you have dealloacated the cursor.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp