A cursor’s existence depends on its scope, which refers to the context in which the cursor is used:
within a user session
within a stored procedure
within a trigger
Within a user session, the cursor exists only until a user ends the session. After the user logs off, Adaptive Server deallocates the cursors created in that session.The cursor does not exist for additional sessions that other users start
If a declare cursor statement is part of a stored procedure or trigger, the cursor created within it applies to that scope and to the scope that launched the stored procedure or trigger. However, cursors declared inside a trigger on an inserted or a deleted table are inaccessible to any nested stored procedures or triggers. Such cursors are accessible within the scope only of the trigger inside which they were declared. Once the stored procedure or trigger completes, Adaptive Server deallocates the cursors created within it.
A cursor name must be unique within a given scope. Adaptive Server detects name conflicts within a particular scope only during runtime. A stored procedure or trigger can define two cursors with the same name if only one is executed. For example, the following stored procedure works because only one names_crsr cursor is defined in its scope:
create procedure proc2 @flag int as if @flag > 0 declare names_crsr cursor for select au_fname from authors else declare names_crsr cursor for select au_lname from authors return
