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
@@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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With