SQL Server cursor Use Step example (create cursor close cursor) _mssql

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

Using cursors

Steps to use cursors:

Before you use a cursor, you must declare (define) it. This process does not actually retrieve data, it simply defines the SELECT statement and cursor options to use.
Once declared, you must open the cursor for use. This process uses the previously defined SELECT statement to actually retrieve the data.
For cursors filled with data, the rows are fetched (retrieved) as needed.
When you end a cursor, you must close the cursor and, if possible, release the cursor (depending on the specific DBMS).
After you declare a cursor, you can turn the cursor on and off frequently as needed. The fetch operation can be performed frequently as needed when the cursor is opened.

Creating cursors

Use the declare named cursor in SQL Server and define the corresponding SELECT statement, with where and other clauses as needed, as shown in the following example:

Copy Code code as follows:

DECLARE Custcursor CURSOR
For
SELECT * from Customers
WHERE Cust_email is NULL

Using cursors

Use the Open CURSOR statement to open the cursor and use the FETCH statement to access the cursor data. The fetch indicates which rows to retrieve, where to retrieve them, and where to place them (such as variable names), and here is an example of using cursors in SQL Server:

Copy Code code 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, and the FETCH statement retrieves a row and saves the value to those variables. Use a while loop to process each row, while the condition while @ @FETCH_STATUS = 0 Terminates processing (exit loop) when no more rows are fetched. This example also does not carry on the concrete processing, in the actual code, should use the concrete processing code to replace it ... Placeholders.

Close cursor

Close cursors in SQL Server:

Copy Code code as follows:

Close Custcursor
Deallocate CURSOR Custcursor

The close statement is used to turn off the cursor. Once the cursor is closed, it cannot be used if it is not opened again. When you use it for the second time, you don't need to declare it, just open it by opening it.