In stored proc, inner cursor needs variable from outer cursor

[Former Member]

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]

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

DEALLOCATE CURSOR 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.

[former_member182090]

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.