SQL Server Cursor with example

hey guys! you know that 'cursor' is little confusing concept in sql server,there are many blogs given this concept in his/her way but not in understandably way.Here I am trying to give concept of 'cursor' in easy way.
CURSOR:-  Cursor is a variable in SQL Server Database which is used for row-by row operations. The cursor is so named because it indicates the current position in the resultset.

T-SQL retrieval operations work with sets of rows known as result sets. The rows returned are all the rows that match a SQL statement, zero or more of them. Using simple SELECT statements, there is no way to get the first row, the next row, or the previous 10 rows.

Sometimes there is a need to step through rows forward or backward, and one or more at a time. This is what cursors are used for. A cursor is a database query stored in SQL Server, not a SELECT statement, but the result set retrieved by that statement.

So finally we can say that -'Cursor is a database objects to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time.'

Life Cycle of cursor-
1.Declare cursor-Before a cursor can be used, it must be declared (defined). This
process does not actually retrieve any data; it merely defines the
SELECT statement to be used.
Syntax:

DECLARE cursor_name CURSOR

 [LOCAL | GLOBAL] --define cursor scope

 [FORWARD_ONLY | SCROLL] --define cursor movements (forward/backward)

 [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] --basic type of cursor

 [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks

 FOR select_statement --define SQL Select statement

 FOR UPDATE [col1,col2,...coln] --define columns that need to be updated 

2.Open -After it is declared, the cursor must be opened for use.
Syntax:

OPEN [GLOBAL] cursor_name --by default it is local 

3.Fetch-With the cursor populated with data, individual rows can be
fetched  as needed.
Syntax:

FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]

FROM [GLOBAL] cursor_name

INTO @Variable_name[1,2,..n]

4.Close-Once the desired data has been fetched, the cursor must be
closed.
Syntax:

CLOSE cursor_name --after closing it can be reopen

5.Deallocate-Finally the cursor must be deallocate.After deallocating, it delete the cursor definition and released all the system resources associated with the cursor.
Syntax:

DEALLOCATE cursor_name --after deallocation it can't be reopen

Now understand whole concept is understanding in following example-
Suppose we have following student table-

CREATE TABLE [dbo].[student](

 [name] [varchar](50) NULL,

 [address] [varchar](50) NULL

)


INSERT INTO [login].[dbo].[student] ([name],[address]) VALUES('Randhir','Basti')



INSERT INTO [login].[dbo].[student] ([name],[address]) VALUES('Atul','Varanasi')



INSERT INTO [login].[dbo].[student] ([name],[address]) VALUES('Brijesh','Varanasi')



INSERT INTO [login].[dbo].[student] ([name],[address]) VALUES('Anoop','raiBareily')



INSERT INTO [login].[dbo].[student] ([name],[address]) VALUES('Shyam','Ghazipur')



INSERT INTO [login].[dbo].[student] ([name],[address]) VALUES('Sachin','Varanasi')

after executing above query data looks in following way-

Now applying cursor on this table to fetch and process data one by one in following way-

-- Local variables

DECLARE @Student_name varchar(50);

DECLARE @Student_Address varchar(50);

-- Define the cursor

DECLARE Students_cursor CURSOR

FOR

SELECT * FROM Student ;

-- Open cursor (retrieve data)

OPEN Students_cursor;

-- Perform the first fetch (get first row)

FETCH NEXT FROM Students_cursor INTO @Student_name,@Student_Address;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

--@@FETCH_STATUS returns the status of the last cursor FETCH statement issued against  

--  any cursor currently opened by the connection. 

--   – @@FETCH_STATUS = 0 means The FETCH statement was successful. 

--   – @FETCH_STATUS = -1 The FETCH statement failed or the row was beyond the result set. 

--   – @@FETCH_STATUS = -2 The row fetched is missing. 

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT 'Name-'+ @Student_name +',Address-'+ @Student_Address

-- This is executed as long as the previous fetch succeeds.

FETCH NEXT FROM Students_cursor INTO @Student_name,@Student_Address;

END

-- Close cursor

CLOSE Students_cursor

-- And finally, remove it

DEALLOCATE Students_cursor;

After running , It will produce result like this -

I hope, Now you are able to understand cursor in sql server.
To acquire more programming knowledge about dot net and sql server,Please keep in touch.