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

By
Advertisement
Introduction:

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

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








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

0 comments:

Post a Comment

Online Casino