Showing results for 
Search instead for 
Did you mean: 

In stored proc, inner cursor needs variable from outer cursor

Former Member
0 Kudos

I recently came over to Sybase ASE SQL from Microsoft SQL Server.  In SQL Server I would often create stored procedures with a nested cursor in which the SELECT statement for the inner cursor contains a WHERE clause with a variable fetched by the outer cursor.  I know that in Sybase the DECLARE CURSOR statement needs to be in a separate batch, so I’m not sure how this can be done.  Any ideas? Thanks.

P. S. I know that cursors aren't ideal but in some situations they're a good solution.

Former Member
0 Kudos

THANK YOU!  These replies really helped.  Yes, I was doing this in ad-hoc SQL rather than in a stored procedure.

I realized that in order to do various calculations, I needed to use a temp table so I decided to do this batch script with a single cursor that references a multi-table select statement, and then I use the temp table to hold temporary values for my calculations.

One thing I'm noticing though: even though at the end I have

CLOSE mycursor


...when I try to run the script a second time I sometimes get the error:

There is already another cursor with the name 'mycursor' at the nesting level '0'.

And I always get the error "There is already an object named '#temp' in the database."

I'm not sure why the CLOSE sometimes isn't closing the cursor, and why the temp table persists after the script terminates.  Thanks for any help.

Accepted Solutions (0)

Answers (1)

Answers (1)

Active Participant
0 Kudos

There is a subtle difference between ASE cursors declared inside a stored procedure ("server cursors") and cursors declared in ad-hoc SQL ("language cursors").

In ASE, you can declare as many cursors as you like inside a stored procedure. By definition, this is one batch since it all sits inside the CREATE PROCEDURE statement.

For a language cursor in ad-hoc SQL, you must indeed put the DECLARE...CURSOR statement in aseparate batch.