Cursor in SQL with example

A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed.

To use cursors in SQL procedures, you need to do the following:

1. Declare a cursor that defines a result set.
2. Open the cursor to establish the result set.
3. Fetch the data into local variables as needed from the cursor, one row at a time.
4. Close the cursor when done
5. Deallocate  the cursor

To work with cursors you must use the following SQL statements:

• DECLARE CURSOR
• OPEN
• FETCH
• CLOSE
• DEALLOCATE

Declare @Firstname varchar(50)

Declare @Lastname varchar(50)

DECLARE @Email varchar(20)

DECLARE Student_cursor CURSOR FOR

Select Firstname,Lastname,Email from from student

OPEN Student_cursor;

FETCH NEXT FROM Student_cursor

INTO @Firstname, @Lastname, @Email;

WHILE @@FETCH_STATUS = 0

BEGIN

print ‘---------------‘

print @Firstname

print @Lastname

print @Email

print ‘---------------‘

FETCH NEXT FROM Student_cursor

    INTO @Firstname, @Lastname, @Email;

END

CLOSE Student_cursor;

DEALLOCATE Student_cursor;

Check the status of Cursor

--Create a cursor.

DECLARE cur CURSOR

Display the status of the cursor after declare

SELECT CURSOR_STATUS('global','cur') return -1

 OPEN cur

Display the status of the cursor after open

SELECT CURSOR_STATUS('global','cur') return 1

CLOSE cur

Display the status of the cursor after close

SELECT CURSOR_STATUS('global','cur')return -1

--Remove the cursor.

DEALLOCATE cur