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

Butts

And why you can't use @@FETCH_STATUS itself? – Custom

And do you really need a cursor, they are often the worst choice. wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them – Prawn

Answer 1

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

Trisyllable

I believe you can also use @@CURSOR_ROWS with KEYSET cursors. – Samoyed

why must be either STATIC or KEYSET cursors for @@CURSOR_ROWS? – Demurrage

Answer 2

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)

Albumenize

Answer 3

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
)
...

Kimes

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 .... – Butts