SQL Server cursor usage example (create a cursor to close the cursor)

Cursor Is a database query stored on the DBMS server. It is not a SELECT statement, but a result set retrieved by this statement. After the cursor is stored, the application can scroll or browse the data as needed.

Use cursor

To use a cursor:

Before using a cursor, you must declare (define) it. This process does not actually retrieve data. It only defines the SELECT statement and cursor options to be used.
Once declared, you must open the cursor for use. In this process, the data is actually retrieved using the SELECT statement defined earlier.
For a cursor with data filled in, retrieve (retrieve) each row as needed.
When you end a cursor, you must close the cursor. If possible, release the cursor (depending on the specific DBMS ).
After the cursor is declared, the cursor can be opened and closed frequently as needed. When the cursor is opened, you can perform the fetch operation frequently as needed.
 

Create a cursor

Use DECLARE in SQL Server to name the cursor and define the corresponding SELECT statement. The WHERE clause and other clauses are included as needed. The example is as follows:
Copy codeThe Code is as follows:
DECLARE CustCursor CURSOR
FOR
SELECT * FROM MERs
WHERE cust_email IS NULL

Use cursor

Use the open cursor statement to OPEN the CURSOR and use the FETCH statement to access the CURSOR data. FETCH specifies the rows to be retrieved, where they are retrieved, and where they are stored (such as variable names). The following is an example of using a cursor in SQL Server:

Copy codeThe Code is as follows:
DECLARE @ cust_id CHAR (10 ),
@ Cust_name CHAR (50 ),
@ Cust_address CHAR (50 ),
@ Cust_city CHAR (50 ),
@ Cust_state CHAR (5 ),
@ Cust_zip CHAR (10 ),
@ Cust_country CHAR (50 ),
@ Cust_contact CHAR (50 ),
@ Cust_email CHAR (255)
OPEN CustCursor
Fetch next from CustCursor
INTO @ cust_id, @ cust_name, @ cust_address,
@ Cust_city, @ cust_state, @ cust_zip,
@ Cust_country, @ cust_contact, @ cust_email
WHILE @ FETCH_STATUS = 0
BEGIN

Fetch next from CustCursor
INTO @ cust_id, @ cust_name, @ cust_address,
@ Cust_city, @ cust_state, @ cust_zip,
@ Cust_country, @ cust_contact, @ cust_email
...
END
CLOSE CustCursor

In this example, a variable is declared for each retrieved column. The FETCH statement retrieves a row and saves the value to these variables. Use the WHILE loop to process each row. The condition WHILE @ FETCH_STATUS = 0 stops processing (exit the loop) When no more rows are obtained ). In this example, no specific processing is required. In actual code, replace the specific processing code with... Placeholder.

Close cursor

Close the cursor in SQL Server:
Copy codeThe Code is as follows:
CLOSE CustCursor
Deallocate cursor CustCursor

The CLOSE statement is used to CLOSE the cursor. Once the cursor is closed, it cannot be used if it is not opened again. The second time you use it, you do not need to declare it again. You only need to OPEN it with OPEN.