Example of steps to use sqlserver cursor (create cursor, close cursor)

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

Using Cursors

Steps to use cursors:

Before using a cursor, you must declare (define) it. This procedure doesn't actually retrieve the data, it just defines the SELECT statement and cursor options to be used.

Once declared, the cursor must be opened for use. This process uses the SELECT statement defined earlier to actually retrieve the data.

For a cursor filled with data, fetch (retrieve) rows as needed.

When ending the use of the cursor, you must close the cursor and, if possible, release the cursor (depends on the specific DBMS).

After declaring a cursor, it can be opened and closed as frequently as needed. While the cursor is open, fetch operations can be performed as frequently as necessary.

Create a cursor

Use DECLARE to name the cursor in SQL Server, and define the corresponding SELECT statement, with WHERE and other clauses as needed. The example is as follows:

The code is as follows:

DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL

Use the cursor

Use the OPEN CURSOR statement Open the cursor and use the FETCH statement to access the cursor data. FETCH indicates which rows to retrieve, where to retrieve them and where to put them (as variable names). Here is an example of using a cursor in SQL Server:

The 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, for each Declare a variable for each retrieved column, and the FETCH statement retrieves a row and saves the values ​​into these variables. Use a WHILE loop to process each row. The condition WHILE @@FETCH_STATUS = 0 terminates processing (exits the loop) when no more rows can be fetched. This example does not perform specific processing. In actual code, the ... placeholder should be replaced with specific processing code.

Close the cursor

Close the cursor in SQL Server:

The 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 without opening it again. There is no need to declare it again when you use it for the second time, just open it with OPEN.