CODEWITHSUNDEEP
sql-servercursor
user1236803
user1236803

Reputation: 183

Cursor not looping correctly

I have a cursor that I want to loop through a staging table, and merge each record into another table.

I cant get this cursor just to loop through the records and return a count.

DECLARE @curCatalogID int
DECLARE @curNomenclature varchar(200)
DECLARE @curMainCategory varchar(200)
DECLARE @curSubCategory varchar(200)
DECLARE @curManufacturer varchar(200)
DECLARE @curModelNo varchar(200)
DECLARE @curPrice varchar(200)
DECLARE @curProductDesc varchar(2000)
DECLARE @curImage varchar(200)
DECLARE @curPDFName varchar(200)
DECLARE @curInventory varchar(200)
DECLARE @curBatchID int
DECLARE @curAuditID int

DECLARE @nCnt int
SET @nCnt = 0

DECLARE import_loop CURSOR FOR
SELECT * FROM tblCatalogStaging

OPEN import_loop
FETCH NEXT FROM import_loop 
INTO    @curCatalogID,
        @curNomenclature,
        @curMainCategory,
        @curSubCategory,
        @curManufacturer,
        @curModelNo,
        @curPrice,
        @curProductDesc,
        @curImage,
        @curPDFName,
        @curInventory,
        @curBatchID,
        @curAuditID

WHILE @@FETCH_STATUS = 0
BEGIN

SET @nCnt = @@ROWCOUNT;

        FETCH NEXT FROM import_loop 
        INTO    @curCatalogID,
        @curNomenclature,
        @curMainCategory,
        @curSubCategory,
        @curManufacturer,
        @curModelNo,
        @curPrice,
        @curProductDesc,
        @curImage,
        @curPDFName,
        @curInventory,
        @curBatchID,
        @curAuditID

END
CLOSE import_loop
DEALLOCATE import_loop

SELECT @nCnt

It should just return 1 value of 2036 ( number of rows in the staging table ) but im getting back like 2036 rows affected, 4072 rows affected, etc etc

Upvotes: 0

Views: 813

Answers (3)

TimothyAWiseman
TimothyAWiseman

Reputation: 14863

@@Rowcount provides the number of rows that were affected by the last statemenet to be executed. I do not think this is what you want, and I would agree with ebyrob about just using a counter variable.

But that is if you really need to do this in a cursor. AS marc_s suggest, you may want to rework your actual procedure so that it is using sets. If that is not possible and you need to, as you said in your response, deal with exceptions in your loop, you may want to look at Phil Factors recent article on that topic.

Upvotes: 0

user645280
user645280

Reputation:

I'm not so sure @@ROWCOUNT is meant to be used inside a CURSOR.

You might have better luck with:

DECLARE @nCnt INT
SET @nCnt = 0
...
SET @nCnt = @nCnt + 1;

Note: A TRIGGER is probably the right place to be doing this validation on row insert/update. Unless you really only want the validation to happen sometimes. (Also, it's SQL errors you'll be raising, not exceptions)

Upvotes: 2

anna
anna

Reputation: 1011

i m not sure if its as simple as this but do you just want:

select count (*) FROM tblCatalogStaging

Upvotes: 0

Related Questions