A cursor is a set of rows together with a pointer that identifies a current row. In other word, Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, it’s like recordset in the ASP and visual basic.
DECLAREcursor_name CURSOR [ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
[ FOR UPDATE [ OF column_name [ ,…n ] ] ]
Is the name of the SQL server cursor defined.
Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created.
Specifies that the scope of the cursor is global to the connection. This cursor is only implicitly deallocated at disconnect.
If neither GLOBAL or LOCAL is specified, the default is “LOCAL”
Specifies that the cursor can only be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option.
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.
Specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. The set of keys that uniquely identify the rows is built into a table in tempdb known as the keyset. If the query references at least one table without a unique index, the keyset cursor is converted to a static cursor.
Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor.
Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified.
Prevents updates made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement.
Specifies that positioned updates or deletes made through the cursor are guaranteed to succeed. SQL Server locks the rows as they are read into the cursor to ensure their availability for later modifications. SCROLL_LOCKS cannot be specified if FAST_FORWARD or STATIC is also specified.
Specifies that positioned updates or deletes made through the cursor do not succeed if the row has been updated since it was read into the cursor. SQL Server does not lock rows as they are read into the cursor. It instead uses comparisons of timestamp column values, or a checksum value if the table has no timestamp column, to determine whether the row was modified after it was read into the cursor. If the row was modified, the attempted positioned update or delete fails. OPTIMISTIC cannot be specified if FAST_FORWARD is also specified.
Specifies that a warning message is sent to the client when the cursor is implicitly converted from the requested type to another.
FOR UPDATE [OF column_name [,…n]]
Defines updatable columns within the cursor. If OF column_name [,…n] is supplied, only the columns listed allow modifications. If UPDATE is specified without a column list, all columns can be updated, unless the READ_ONLY concurrency option was specified.
I have a table called students and it has few records of students
CREATE TABLE dbo.Students(Id INT IDENTITY,Name VARCHAR(30))
INSERT INTO dbo.Students(Name) VALUES(‘Kanna Dasan’)
INSERT INTO dbo.Students(Name) VALUES(‘Ram Kumar’)
INSERT INTO dbo.Students(Name) VALUES(‘Xavier Selvaraj’)
INSERT INTO dbo.Students(Name) VALUES(‘Kaviyarasan’)
Now below cursor code will fetch record by record and print the name of the student.
SET@getStudentName = CURSORFOR
–Fetching from cursor
FROM@getStudentName INTO @StudentName
–Loop starts here
WHILE @@FETCH_STATUS = 0
–Fetching next value to local variable from cursor
FROM@getStudentName INTO @StudentName
–Closing the cursor
The Output will be
Incoming search terms
how to create cursor in sql server 2008 with example
deallocate cursor sql server
sql server create cursor from dynamic sql
sql server cursor example
cursor in sql server 2005 tutorial
cursor in sql server 2005 example
cursors in sql server 2008 tutorial
sample cursor in sql server 2005