0

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.

Improve this question

1 Answer 1

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.