counting rows before processing with a cursor tsql

Question

hi there

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

nat

Answer 1

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

Answer 2

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