counting rows before processing with a cursor tsql

Question

I have a SQL Server sp using a cursor thus:

DECLARE TestCursor CURSOR FOR
    SELECT
        tblHSOutcomes.strOutcomeName, 
        tblHSData.fkHSTest
    FROM
        tblHSData 
        INNER JOIN tblHSOutcomes ON tblHSData.fkOutcome = tblHSOutcomes.uidOutcome 
        INNER JOIN tblHSTests ON tblHSData.fkHSTest = tblHSTests.uidTest
    WHERE
        tblHSData.fkEpisode = @uidHSEpisodes

OPEN TestCursor
    FETCH NEXT FROM TestCursor
    INTO @Result, @TestID

WHILE @@FETCH_STATUS = 0
BEGIN
...etc

It's working fine , however it would be nice to be able to check if the cursors query has any records before continuing to process through it. if there a @@ var that I can use to check this?

I know there is @@RowCount - but this has only the current number of rows processed - so isn't very helpful

Ideally I would like to be able to do something like this:

if @@cursorQueryHasRecords 
BEGIN
WHILE @@FETCH_STATUS = 0
BEGIN
...etc

thanks

And do you really need a cursor, they are often the worst choice. http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them — HLGEM, Sep 17 '10 at 13:26

score 15 · Accepted Answer · answered Sep 17 '10 at 11:11

15

If you are able to declare your cursor as STATIC then you can use the built in function @@Cursor_Rows

Cursor Options (Static/ReadOnly/Dynamic)

@@Cursor_Rows

answered Sep 17 '10 at 11:11

codingbadger

42,678
13
95
110

2

I believe you can also use @@CURSOR_ROWS with KEYSET cursors. – Baodad Apr 12 '17 at 21:49
why must be either STATIC or KEYSET cursors for @@CURSOR_ROWS? – user1169587 Apr 17 '23 at 03:55

score 5 · Answer 2 · answered Dec 20 '19 at 16:16

Here is a example of how to use @@Cursor_Rows

DECLARE TestCursor CURSOR STATIC FOR
  SELECT <snip>

OPEN TestCursor

IF @@Cursor_Rows > 0 BEGIN

  FETCH NEXT FROM TestCursor INTO @compid, @logid, @category
  WHILE @@FETCH_STATUS = 0 BEGIN   

  <snip>
END

CLOSE TestCursor
DEALLOCATE TestCursor

Please note that you need to declare the cursor STATIC (or KEYSET)

score 4 · Answer 3 · answered Sep 17 '10 at 10:52

4

if exists(
    SELECT
        tblHSOutcomes.strOutcomeName, 
        tblHSData.fkHSTest
    FROM
        tblHSData 
        INNER JOIN tblHSOutcomes ON tblHSData.fkOutcome = tblHSOutcomes.uidOutcome 
        INNER JOIN tblHSTests ON tblHSData.fkHSTest = tblHSTests.uidTest
    WHERE
        tblHSData.fkEpisode = @uidHSEpisodes
)
...

answered Sep 17 '10 at 10:52

Denis Valeev

5,975
35
41

1

hi thanks for the reply. how to i wrap that in the cursor bit.. dont want to run that query twice.. Presumably i cant do decalre testcursor cursor for if exists(select .... – nat Sep 17 '10 at 11:09

counting rows before processing with a cursor tsql

3 Answers3