SQL Server Performance

how to check cursor is exists before close them

Discussion in 'SQL Server 2005 General Developer Questions' started by yhchan2005, Nov 29, 2010.

  1. yhchan2005 Member

    hi
    i have below script in store procedure :
    ///////////////////////////////////////
    Begin Transaction
    Begin Try
    Declare csXXXX Cursor For
    Open csXXXX
    Fetch Next From csXXXX into ......
    while @@FETCH_STATUS = 0 begin
    ...
    ...
    Fetch Next From csXXXX into .......
    end
    Close csXXXX
    DeAllocate csXXXX
    End Try
    Begin Catch
    If @@TRANCOUNT > 0 Rollback Transaction
    End Catch
    If @@TRANCOUNT > 0 begin
    Commit Transaction
    end
    ///////////////////////////////////////
    notice that if some error raise during the while @@Fetch_status, SQL will jump
    to begin catch portion and the cursor will not close. If i execute again the same
    store procedure, SQL server will prompt 'Cursor csXXX already exists..
    i try to put below script after the begin catch
    Close csXXXX
    DeAllocate csXXXX
    but if the error raise before the open csXXXX, then might have another error due to cursor not exists.
    how to check whether the cursor is exists or any other alternate solution ?
    Please help...
  2. FrankKalis Moderator

    Have a look at CURSOR_STATUS() in Books Online.

Share This Page