>>---EXEC SQL---.------------.---> +-AT db_name-+ >--CLOSE---cursor_name---.------------.---END-EXEC--->< +WITH RETURN-+
|AT db_name||The name of a database that has been declared using DECLARE DATABASE. This clause is not required, and if omitted, the connection automatically switches to the connection associated with the DECLARE CURSOR statement if different than the current connection, but only for the duration of the statement. Provided for backward compatibility.|
|cursor_name||A previously declared and opened cursor.|
The cursor must be declared and opened before it can be closed. All open cursors are closed automatically at the end of the program.
You can specify WITH RETURN in a SQL CLR stored procedure not compiled with DIALECT=MAINFRAME. If you do so, the client application must use the GET NEXT RESULT SET statement to retrieve the result set(s).
*Declare the cursor... EXEC SQL DECLARE C1 CURSOR FOR SELECT staff_id, last_name FROM staff END-EXEC IF SQLCODE NOT = ZERO DISPLAY 'Error: Could not declare cursor.' DISPLAY SQLERRMC DISPLAY SQLERRML EXEC SQL DISCONNECT ALL END-EXEC STOP RUN END-IF EXEC SQL OPEN C1 END-EXEC IF SQLCODE NOT = ZERO DISPLAY 'Error: Could not open cursor.' DISPLAY SQLERRMC DISPLAY SQLERRML EXEC SQL DISCONNECT CURRENT END-EXEC STOP RUN END-IF PERFORM UNTIL sqlcode NOT = ZERO *SQLCODE will be zero as long as it has successfully fetched data EXEC SQL FETCH C1 INTO :staff-staff-id, :staff-last-name END-EXEC IF SQLCODE = ZERO DISPLAY "Staff ID: " staff-staff-id DISPLAY "Staff member's last name: " staff-last-name END-IF END-PERFORM EXEC SQL CLOSE C1 END-EXEC IF SQLCODE NOT = ZERO DISPLAY 'Error: Could not close cursor.' DISPLAY SQLERRMC DISPLAY SQLERRML END-IF