Microsoft SQL Server Cursor statements
In embedded SQL,
statements that retrieve data can involve cursors. These statements
are:
-
DECLARE cursor_name CURSOR
FOR … -
OPEN cursor_name
-
FETCH cursor_name INTO
… -
CLOSE cursor_name

UPDATE ... WHERE CURRENT OF cursor_name
and DELETE
are
... WHERE CURRENT OF cursor_name
not supported in SQL Server.
Retrieval
Retrieval using cursors is conceptually similar to retrieval
in the singleton SELECT. The main difference is that since there
can be multiple rows in a result set, you control when the next
row is fetched into the PowerScript data variables.
If you expect only a single row to exist in the employee table
with the specified emp_id, use the singleton SELECT. In
a singleton SELECT, you specify the SELECT statement and destination
variables in one concise SQL statement:
1 |
SELECT emp_name, emp_salary<br> INTO :emp_name_var, :emp_salary_var<br> FROM employee WHERE emp_id = :emp_id_var; |
However, when a SELECT may return multiple rows, you must:
-
Declare a cursor.
-
Open it (which conceptually executes the SELECT).
-
Fetch rows as needed.
-
Close the cursor.
Declaring and opening a cursor
Declaring a cursor is tightly coupled with the OPEN statement.
The DECLARE specifies the SELECT statement to be executed, and the
OPEN actually executes it.
Declaring a cursor is similar to declaring a variable; a cursor
is a nonexecutable statement just like a variable declaration. The
first step in declaring a cursor is to define how the result set
looks. To do this, you need a SELECT statement. Since you must refer
to the result set in subsequent SQL statements, you must associate
the result set with a logical name.
Scrolling and locking
Use the CursorScroll and CursorLock DBParm parameters to specify
the scrolling and locking options.
Example
Assume the SingleLineEdit sle_1 contains the state
code for the retrieval:
1 |
// Declare cursor emp_curs for employee table<br>// retrieval.<br>DECLARE emp_curs CURSOR FOR<br> SELECT emp_id, emp_name FROM EMPLOYEE<br> WHERE emp_state = :sle_1.text; |
1 |
// Declare local variables for retrieval.<br>string emp_id_var<br>string emp_name_var |
1 |
// Execute the SELECT statement with<br>// the current value of sle_1.text.<br>OPEN emp_curs; |
1 |
// At this point, if there are no errors,<br>// the cursor is available for further<br>// processing. |