Recurvice SP and Cursor

• Ali Shah

  Valued Member

  Points: 60

  More actions

  April 8, 2004 at 1:43 pm

  #108118

  Hi all

  I have a problem related to Stored procedure. i have actually created a SP which creates a Cursor and recursively calls itself. but when on second call it call itself it says:

  Cursor already exists ....

  is there anyother way so that i can create new cursor with different name on every call.

  Pseduo code

  For 1 to 10

   

• Mike Good

  SSCertifiable

  Points: 7415

  More actions

  April 8, 2004 at 8:34 pm

  #502243

  Problem is global cursors.  Can fix with TSQL cursor declare syntax instead of ANSI-92:

              declare c0 cursor local fast_forward for ...

  Or you can change the DB option CURSOR_DEFAULT value to local to set this for your entire DB, after which you can use the ANSI syntax if you prefer.

              alter database pubs set cursor_default local

• Kashibabu

  SSC Rookie

  Points: 25

  More actions

  April 10, 2004 at 6:47 am

  #502365

  write down these two statements at the end

   

  close cursorname

  deallocate cursorname