Post reply

Difference between @@Cursorrows and @@FetchStatus In SQL Cursors

  • October 31, 2017 at 1:27 pm

    #409739

    Hi,
    Sorry If it is a lame question. I am trying to understand the cursors. 
    I dont understand the difference between @@Cursorrows and @@FetchStatus

    1. DECLARE @Id int
    2. DECLARE @name varchar(50)
    3. DECLARE Dynamic_cur_empupdate CURSOR
    4. DYNAMIC
    5. FOR
    6. SELECT EmpID,EmpName from Employee ORDER BY EmpName
    7. OPEN Dynamic_cur_empupdate
    8. IF @@CURSOR_ROWS > 0
    9. BEGIN
    10. FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id,@name
    11. WHILE @@Fetch_status = 0
    12. BEGIN
    13. IF @name='Mohan'
    14. Update Employee SET Salary=15000 WHERE CURRENT OF Dynamic_cur_empupdate
    15. FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id,@name
    16. END
    17. END
    18. CLOSE Dynamic_cur_empupdate
    19. DEALLOCATE Dynamic_cur_empupdate
    20. Go
    21. Select * from Employee

    I understand that @@Countrows >0 mean "If there are rows" fetch the rows into...@Id,@Name
    why again fetch_status=0??I read somewhere that it returns 0 as long as there are rows in the table. I don't understand as these both looks same

    Appreciate your time and help

  • October 31, 2017 at 1:38 pm

    #1966066

    @@FETCH_STATUS will indicate if rows are successfully brought back from the cursor, @@CURSOR_ROWS returns the number of qualifying rows currently in the last cursor opened on the connection. Obviously you want to use the former for controling the cursor loop.
    😎

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply