Cursor in sql server

Cursor is a database object 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. We can use cursor when we need to update records in a database table row by row. Operations in a relational database act on a complete set of rows. This complete set of rows returned by the statement is known as the result set.

Life cycle of the cursor

1.   Declare cursor
2.   Open cursor
3.   Fetch records
4.   Close the cursor
5.   De allocate the cursor

Syntax to Declare Cursor

 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

See the example

Here I am feting the data from a table and using cursor I am printing these records.

DECLARE @Id int DECLARE @name varchar(50) DECLARE @empAdd varchar(50) DECLARE @Mobile Varchar(10) DECLARE cur_emp CURSOR STATIC FOR select * from emp OPEN cur_emp IF @@CURSOR_ROWS > 0  BEGIN  FETCH NEXT FROM cur_emp INTO @Id,@name,@empAdd,@Mobile  WHILE @@Fetch_status = 0  BEGIN  PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+   ', Address : '+ @empAdd + ' , Mobile number: '+ @Mobile  FETCH NEXT FROM cur_emp INTO @Id,@name,@empAdd,@Mobile  END END CLOSE cur_emp DEALLOCATE cur_emp

See the output