CODEWITHSUNDEEP
sqlsql-server
vipul nanavare
vipul nanavare

Reputation: 321

How to resolve “A cursor with the name already exists” error

I have created a cursor in a stored procedure and in that cursor I call another stored procedure.

In that stored procedure, I have created another cursor with the same name.

While running the main stored procedure, I get an error

A cursor with the name already exists

I am confused that I have created cursor with same but in two different stored procedures, then why is this error thrown?

Upvotes: 0

Views: 5693

Answers (1)

vipul nanavare
vipul nanavare

Reputation: 321

After some research I have found its root cause.

When declare cursor I have not specified that the scope of the cursor is local to the batch. And I have checked my database properties where cursor default scope is set as GLOBAL, because of this I have gotten this error while running stored procedure.

So to resolve this error there are two options:

  1. Define a local cursor. Just put the keyword LOCAL after CURSOR:

     declare MyCursor CURSOR LOCAL FOR
 ...

  2. Define Default scope of courser as LOCAL for database.

    Database > Right Click > Properties

enter image description here

Upvotes: 6

Related Questions