Using Cursor in SQL Server

Updated on August 30, 2015     Kisan Patel

A cursor is a database object used retrieve data from a result set one row at a time or row-by-row basis.

Syntax to declare cursor

DECLARE vend_cursor CURSOR
FOR SELECT * FROM Purchasing.Vendor
OPEN vend_cursor
FETCH NEXT FROM vend_cursor

Here is the the simplest example of the Cursor in SQL Server.

DECLARE @student_roll int, @student_name nvarchar(50)

DECLARE student_cursor CURSOR
    FOR SELECT name, roll_no FROM Student
  OPEN student_cursor
      FETCH NEXT FROM student_cursor INTO @student_name, @student_roll
      WHILE @@FETCH_STATUS = 0
      BEGIN
        print @student_name
        FETCH NEXT FROM student_cursor INTO @student_name, @student_roll
      END
  CLOSE student_cursor
DEALLOCATE student_cursor