cusor help

  • i'm trying to use the sp_cursorclose

    procedure. Does anyone know what parameters i need to use this procedure?

    thanks rich

    RICHARD KIRMSS


    RICHARD KIRMSS

  • The system stored procedure sp_cursorclose is an internal system procedure. Typically when we want to close a cursor and remove it from memory we do the following:

    
    
    CLOSE <cursor name>
    DEALLOCATE <cursor name>

    Is there a reason you need to use sp_cursorclose explicitly?

    K. Brian Kelley

    bkelley@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

  • well in my stored procedure that i created i opening and closing 2 different cursors numourous times. And there are times when i get a a message stating:

    Server: Msg 16915, Level 16, State 1, Procedure sp_grant_revoke_perms_roles, Line 65

    A cursor with the name 'tables_cursor' already exists.

    so i thought well maybe i would try the sp_cursorclose. I checked and re-checked my program and everytime that I'm done with the cursor i do close and i deallocate it and I still get that message. any suggestions?

    RICHARD KIRMSS


    RICHARD KIRMSS

  • Is there the possibility of your stored procedure being executed by more than one connection at the same time? If so, are you using local or global cursors?

    K. Brian Kelley

    bkelley@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

  • i'm not sure...i'm just trying to run the procedure form the query analyzer. Inside the procedure is where i created the cursors. The Cusors are local to the procedure.

    RICHARD KIRMSS


    RICHARD KIRMSS

  • ok i figured out what i got to do. BUt i need to know the status of my cursors. is there a way i can find out the status of my cursors? thanks

    rich

    RICHARD KIRMSS


    RICHARD KIRMSS

  • use CURSOR_STATUS

    From BOL:

    CURSOR_STATUS

    A scalar function that allows the caller of a stored procedure to determine whether or not the procedure has returned a cursor and result set for a given parameter.

    Syntax

    CURSOR_STATUS

    (

    { 'local' , 'cursor_name' }

    | { 'global' , 'cursor_name' }

    | { 'variable' , 'cursor_variable' }

    )

    Arguments

    'local'

    Specifies a constant that indicates the source of the cursor is a local cursor name.

    'cursor_name'

    Is the name of the cursor. A cursor name must conform to the rules for identifiers.

    'global'

    Specifies a constant that indicates the source of the cursor is a global cursor name.

    'variable'

    Specifies a constant that indicates the source of the cursor is a local variable.

    'cursor_variable'

    Is the name of the cursor variable. A cursor variable must be defined using the cursor data type.

    Return Types

    smallint

Viewing 7 posts - 1 through 6 (of 6 total)

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