Problem in Stored Procedure

Question

0.00/5 (No votes)

See more:

Hello There

There is a confusion between @@ROWCOUNT and @@CURSOR_ROWS.
I want to fetch data with Select query and stored it into the CURSOR as:-

SQL

DECLARE CUR_Invoice CURSOR FOR
SELECT * FROM  Invoice
OPEN CUR_Invoice
FETCH NEXT FROM CUR_Invoice INTO @SUB_CODE,@VOU_NO,@VOU_DATE,@AMOUNT...

Now I want to check if the records are fetched with the above query.
For this, should i use either @@ROWCOUNT or @@CURSOR_ROWS.
E.g.:-

SQL

IF(@@CURSOR_ROWS>0)
BEGIN
   PRINT 'RECORDS AVAILABLE';
END
CLOSE CUR_Invoice
DEALLOCATE CUR_Invoice

---
Need Suggestion
Rgds

Posted 24-Jun-13 18:27pm

Mayank Topiwala

Updated 27-Jul-13 19:27pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

chaau · Answer 1 · 2013-06-24T18:37:00

Solution 1

Usually you use @@FETCH_STATUS.

See this MSDN Article[^] for example.

BTW, if the only reason for this cursor to check if there are records available in the table you could use EXISTS:

SQL

IF EXISTS(SELECT 1 FROM Invoice)
BEGIN
   PRINT 'RECORDS AVAILABLE';
END

Posted 24-Jun-13 18:37pm

chaau

Updated 24-Jun-13 18:45pm

v2

Comments

Mayank Topiwala 25-Jun-13 0:53am

Thanx Andrew. Plz Check if below statement is valid for n no. of rows:

Declare @countValue INT
Declare CUR_Invoice CURSOR FOR
Select * From Invoice
OPEN CUR_Invoice
FETCH NEXT FROM CUR_Invoice INTO @......
While(@@FETCH STATUS = 0)
BEGIN
Set @countValue = @@ROWCOUNT;
BREAK;
FETCH NEXT FROM CUR_Invoice INTO @......
END
CLOSE ...
DEALLOCATE...

chaau 25-Jun-13 1:06am

I do not understand what is the purpose of these two lines:
Set @countValue = @@ROWCOUNT;
BREAK;
What are you trying to count?

Mayank Topiwala 25-Jun-13 1:10am

The No. of Row Fetched by Select query

chaau 25-Jun-13 1:20am

If you just need the number of rows use this:
Set @countValue = (SELECT COUNT(*) FROM Invoice)

If you really need to do this via the cursor use this:
Declare @countValue INT
Declare CUR_Invoice CURSOR FOR Select * From Invoice
OPEN CUR_Invoice
Set @countValue = 0;
FETCH NEXT FROM CUR_Invoice INTO @......
While(@@FETCH STATUS = 0)
BEGIN
Set @countValue = @countValue + 1;
FETCH NEXT FROM CUR_Invoice INTO @......
END
CLOSE ...
DEALLOCATE...