Sql Server 2005/2008 Cursor Example|Cursor Example in Sql Server 2005/2008|Create cursor in sql server

Introduction:

In my previous article I have explained about SQL Query to backup database in sql server, SQL query to extract file names from a html content, How to set deadlock priority in sql server, Find count of each characters in a string in SQL Server, Use of SET NOCOUNT in sql server, Different Isolation levels in sql server in SQL Server. In this article I am going to explain about how to create cursor in sql server with example in sql server 2005/2008.

Explanation:

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.

Syntax:

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]

     [ FORWARD_ONLY | SCROLL ]

     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

     [ TYPE_WARNING ]

     FOR select_statement

     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

[;]

Arguments:

cursor_name

Is the name of the SQL server cursor defined.

LOCAL

Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created.

GLOBAL

Specifies that the scope of the cursor is global to the connection. This cursor is only implicitly deallocated at disconnect.

Note:

If neither GLOBAL or LOCAL is specified, the default is "LOCAL"

FORWARD_ONLY

Specifies that the cursor can only be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option.

STATIC

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.

KEYSET

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.

DYNAMIC

Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor.

FAST_FORWARD

Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified.

READ_ONLY

Prevents updates made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement.

SCROLL_LOCKS

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.

OPTIMISTIC

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.

TYPE_WARNING

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.

Example:

I have a table called students and it has few records of students

USE Mydb

GO

CREATE TABLE dbo.Students(Id INT IDENTITY,Name VARCHAR(30))

GO

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')

GO

Now below cursor code will fetch record by record and print the name of the student.

DECLARE @StudentName VARCHAR(30)

--Declaring Cursor

DECLARE @getStudentName CURSOR

SET @getStudentName = CURSOR FOR

SELECT Name

FROM dbo.Students

--Opening Curson

OPEN @getStudentName

--Fetching from cursor

FETCH NEXT

FROM @getStudentName INTO @StudentName

--Loop starts here

WHILE @@FETCH_STATUS = 0

BEGIN

--Printing Value

PRINT @StudentName

--Fetching next value to local variable from cursor

FETCH NEXT

FROM @getStudentName INTO @StudentName

END

--Closing the cursor

CLOSE @getStudentName

--Deallocating Cursor

DEALLOCATE @getStudentName

GO

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