CODEWITHSUNDEEP
sql-server-2008database-cursor
Ravi Kumar Singh
Ravi Kumar Singh

Reputation: 187

Calling Cursor in Loop

I have created a procedure(p1). This procedure opens a cursor(c1). And i call another procedure(p2) in loop for each row of cursor. Procedure takes 22 secs to 120 secs. In procedure P1, i open, close and deallocate cursor c1. This works fine.

Now procedure P1 has a input. So ,i have written another procedure (p3) with cursor (c2). Cursor c2 gets input for p1. Now row count for C2 is 1403. So i call P1, 1403 times from P3. And this creates error:

Error: Cursor trying to open C1, is already open.

I understand that Sql server tries to run P1 simultaneously for many rows in C2. And as C1 is already open, So comes the error.

Any idea, how i can restrict P3 to execute P1 one at a time.

Upvotes: 0

Views: 428

Answers (1)

Miika L.
Miika L.

Reputation: 3353

How are you declaring the cursor C1 in P1? Is it a LOCAL or a GLOBAL cursor? A LOCAL cursor should be able to be declared as many times as you like, as its only used within the scope of the single procedure.

DECLARE customerCursor LOCAL

If GLOBAL/LOCAL is not defined for the cursor, it will use the database default value (from CURSOR_DEFAULT) which starts off as GLOBAL.

If the above doesn't help, would it be possible to see simplified versions of your procedures for easier testing?

Regards, M

Upvotes: 2

Related Questions