Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

counting rows before processing with a cursor tsql

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

like image 364
nat Avatar asked Sep 17 '10 10:09

nat


People also ask

How do I count cursor records in SQL?

@@CURSOR_ROWS (Transact-SQL) @@CURSOR_ROWS can be called to determine that the number of the rows that qualify for a cursor are retrieved at the time of the @@CURSOR_ROWS call.

How do I count the number of rows in a cursor?

You can use the cursor_rowCount function within SQL PL contexts and would perform this task whenever in your procedural logic it is necessary to access the count of the number of rows that have been fetched for a cursor so far or the total count of rows fetched.

What is @@ rowcount?

SQL Server @@ROWCOUNT is a system variable that is used to return the number of rows that are affected by the last executed statement in the batch.


1 Answers

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

like image 128
codingbadger Avatar answered Sep 22 '22 02:09

codingbadger