simply we can define,cursor is  a way of taking a set of data,
and being able to interact with a single record at a time in that set.
syntax,
DECLARE <cursor name> CURSOR
FOR <select statement>

for ex,

DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE’

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @TableName
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor,

Deallocate TableCursor,

Cursor,s are classified into the following four types,
•    Static
•    Keyset-driven
•    Dynamic
•    Fast-forward-only
Static Cursor’s:
Simply in-time Cursor.We cannot database update using Static Cursor.
USE Northwind
/* Build the table that we’ll be playing with this time  */
SELECT OrderID, CustomerID
INTO CursorTable
FROM Orders
WHERE OrderID BETWEEN 10701 AND 10705

– Declare our cursor
DECLARE CursorTest CURSOR
GLOBAL                      — So we can manipulate it outside the batch
SCROLL                      — So we can scroll back and see the changes
STATIC                      — This is what we’re testing this time
FOR
SELECT OrderID, CustomerID
FROM CursorTable

– Declare our two holding variables
DECLARE @OrderID int
DECLARE @CustomerID varchar(5)

– Get the cursor open and the first record fetched
OPEN CursorTest
FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID

– Now loop through them all
WHILE @@FETCH_STATUS=0
BEGIN
PRINT CONVERT(varchar(5),@OrderID) + ‘   ‘ +  @CustomerID
FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID
END
– Make a change. We’ll see in a bit that this won’t affect the cursor.
UPDATE CursorTable
SET CustomerID = ‘XXXXX’
WHERE OrderID = 10703

– Now look at the table to show that the update is really there.
SELECT OrderID, CustomerID
FROM CursorTable

– Now go back to the top. We can do this since we have a scrollable cursor
FETCH FIRST FROM CursorTest INTO @OrderID, @CustomerID

– And loop through again.
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CONVERT(varchar(5),@OrderID) + ‘   ‘ +  @CustomerID
FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID
END

– Now it’s time to clean up after ourselves
CLOSE CursorTest

DEALLOCATE CursorTest

DROP TABLE CursorTable
Keyset-Driven Cursor’s:

Keyset cursors can be used as the basis for a cursor that is going
to perform updates to the data.

USE Northwind
/* Build the table that we’ll be playing with this time   */
SELECT OrderID, CustomerID
INTO CursorTable
FROM Orders
WHERE OrderID BETWEEN 10701 AND 10705
– Now create a unique index on it in the form of a primary key
ALTER TABLE CursorTable
ADD CONSTRAINT PKCursor
PRIMARY KEY (OrderID)

/* The IDENTITY property was automatically brought over when
** we did our SELECT INTO, but we want to use our own OrderID
** value, so we’re going to turn IDENTITY_INSERT on so that we
** can override the identity value.
*/
SET IDENTITY_INSERT CursorTable ON

– Declare our cursor
DECLARE CursorTest CURSOR
GLOBAL                 — So we can manipulate it outside the batch
SCROLL                 — We can scroll back to see if the changes are there
KEYSET                 — This is what we’re testing this time
FOR
SELECT OrderID, CustomerID
FROM CursorTable

– Declare our two holding variables
DECLARE @OrderID int
DECLARE @CustomerID varchar(5)

– Get the cursor open and the first record fetched
OPEN CursorTest
FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID

– Now loop through them all
WHILE @@FETCH_STATUS=0
BEGIN
PRINT CONVERT(varchar(5),@OrderID) + ‘ ‘ + @CustomerID
FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID
END

– Make a change. We’ll see that it does affect the cursor this time.
UPDATE CursorTable
SET CustomerID = ‘XXXXX’
WHERE OrderID = 10703

– Now we’ll delete a record so we can see how to deal with that
DELETE CursorTable
WHERE OrderID = 10704

– Now insert a record. We’ll see that the cursor is oblivious to it.
INSERT INTO CursorTable
(OrderID, CustomerID)
VALUES
(99999, ‘IIIII’)

– Now look at the table to show that the update is really there.
SELECT OrderID, CustomerID
FROM CursorTable
– Now go back to the top. We can do this since we have a scrollable cursor
FETCH FIRST FROM CursorTest INTO @OrderID, @CustomerID

/* And loop through again.
** This time, notice that we changed what we’re testing for.
** Since we have the possibility of rows being missing (deleted)
** before we get to the end of the actual cursor, we need to do
** a little bit more refined testing of the status of the cursor.
*/
WHILE @@FETCH_STATUS != -1
BEGIN
IF @@FETCH_STATUS = -2
BEGIN
PRINT ‘  MISSING! It probably was deleted.’
END
ELSE
BEGIN
PRINT CONVERT(varchar(5),@OrderID) + ‘   ‘ +  @CustomerID
END
FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID
END

– Now it’s time to clean up after ourselves
CLOSE CursorTest

DEALLOCATE CursorTest

DROP TABLE CursorTable

Dynamic Cursor’s:

Dynamically created cursor’s,the major difference between keyset and dynamic
is DYNAMIC keyword.

USE Northwind
/* Build the table that we’ll be playing with this time  */
SELECT OrderID, CustomerID
INTO CursorTable
FROM Orders
WHERE OrderID BETWEEN 10701 AND 10705

– Now create a unique index on it in the form of a primary key
ALTER TABLE CursorTable
ADD CONSTRAINT PKCursor
PRIMARY KEY (OrderID)

/* The IDENTITY property was automatically brought over when
** we did our SELECT INTO, but we want to use our own OrderID
** value, so we’re going to turn IDENTITY_INSERT on so that we
** can override the identity value.
*/
SET IDENTITY_INSERT CursorTable ON

– Declare our cursor
DECLARE CursorTest CURSOR
GLOBAL               — So we can manipulate it outside the batch
SCROLL               — So we can scroll back and see if the changes are there
DYNAMIC              — This is what we’re testing this time
FOR
SELECT OrderID, CustomerID
FROM CursorTable

– Declare our two holding variables
DECLARE @OrderID      int
DECLARE @CustomerID   varchar(5)
– Get the cursor open and the first record fetched
OPEN CursorTest
FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID

– Now loop through them all
WHILE @@FETCH_STATUS=0
BEGIN
PRINT CONVERT(varchar(5),@OrderID) + ‘   ‘ +  @CustomerID
FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID
END

– Make a change. We’ll see that it does affect the cursor this time.
UPDATE CursorTable
SET CustomerID = ‘XXXXX’
WHERE OrderID = 10703

– Now we’ll delete a record so we can see how to deal with that
DELETE CursorTable
WHERE OrderID = 10704

– Now insert a record. We’ll see that the cursor is oblivious to it.
INSERT INTO CursorTable
(OrderID, CustomerID)
VALUES
(99999, ‘IIIII’)

– Now look at the table to show that the update is really there.
SELECT OrderID, CustomerID
FROM CursorTable

– Now go back to the top. We can do this since we have a scrollable cursor
FETCH FIRST FROM CursorTest INTO @OrderID, @CustomerID

/* And loop through again.
** This time, notice that we changed what we’re testing for.
** Since we have the possibility of rows being missing (deleted)
** before we get to the end of the actual cursor, we need to do
** a little bit more refined testing of the status of the cursor.
*/
WHILE @@FETCH_STATUS != -1
BEGIN
IF @@FETCH_STATUS = -2
BEGIN
PRINT ‘  MISSING! It probably was deleted.’
END
ELSE
BEGIN
PRINT CONVERT(varchar(5),@OrderID) + ‘   ‘ +  @CustomerID
END
FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID
END

– Now it’s time to clean up after ourselves
CLOSE CursorTest

DEALLOCATE CursorTest

DROP TABLE CursorTable

Fast-Forward Cursor’s:

Default Cursor,just you open the cursor, and do nothing else but deal with the data,
move forward, and deallocate it.

Hope,this helps,

Happy Coding.