EXEC SQL PREPARE MyStmt FROM :hStmt;
EXEC SQL DECLARE MyCursor CURSOR FOR MyStmt;
EXEC SQL SET CURSOR ROWS :hCursorRows FOR MyCursor;
--------------------------------------
--------------------------------------
--------------------------------------
EXEC SQL CLOSE MyCursor;
EXEC SQL DEALLOCATE CURSOR MyCursor;
What is the use of closing and de-allocating the cursor here? Even if we remove the last two lines and call this function again and again it works fine. Tried it with 30000 sequential calls to check for any cursor threshold.
First, some important concepts must be understood.
The CLOSE command is useful because you may want to prevent this cursor from being used later until you set it as OPEN again, however, this command does not remove the object reference, wich means that the memory still compromised with it. Because of that you can't create another cursor with the same name of another closed cursor.
On the other hand, DEALLOCATE, does remove the object reference. This means that the operational system could overwrite memory that was previously associated with the object and after execute this command you will be able to declare another cursor with the same name.
That is, you should want to just CLOSE in case that you're planning to use the object again. Now... why not just DEALLOCATE if you are not planning to use the object again? I mean, CLOSE command sounds redundant, right?
Well... it depends.
The default system behaviour is to declare cursors as GLOBAL. This means that, unless you explicit set the cursor as LOCAL (wich seems not to be your case), procedures called inside a main procedure that has a cursor declared will access this cursor too. That's why the CLOSE command exists and not only DEALLOCATE command: to provide a way to a GLOBAL cursor (which is default) be available through this inner callings.
If only DEALLOCATE existed, when used inside a main procedure, the inner procedures wouldn't have access to the cursor. In this way, there woudn't be reason to a GLOBAL behaviour even exist.
The right practice is use LOCAL cursors for local pourposes.
So, the answer is: If you sure your cursor is LOCAL, there's no pratical difference by using only DEALLOCATE or both (CLOSE and DEALLOCATE), since that DEALLOCATE performs CLOSE as well.
But if your cursor is GLOBAL, it means that you do intend to use it again further (otherwise, set explicit LOCAL) and you may want handle closings and openings as necessary, with only one DEALLOCATE at the end of all callings, OR not even that, because a cursor variable does not have to be explicitly deallocated. The variable is implicitly deallocated when it goes out of scope (reference: http://msdn.microsoft.com/en-us/library/ms188782.aspx).