Working with Cursors in SQL Server.

Cursors are useful thing in SQL as it is enable you to work with a subset of data on a row-by-row basis. All cursor functions are non-deterministic because the results might not always be consistent. A user might delete a row while you are working with your cursor. Here after a few functions that work with cursors.
When you work with Cursor , you will Have to follow these steps .
1. Declare Cursor
2. Open Cursor
3. Run through the cursor
4. Close Cursor
5. Deallocate the Cursor

1. Declare cursor

Declare Emp_Cur Cursor For
Select Emp_Code From Employee_Details

Declare the cursor with select query for a Table/View as shown above.
2. Open Cursor

Open Emp_Cur
Fetch Next From Emp_Cur
Into @mCardNo

Open the Declared cursor and Fetch them into declared local variables for row-by-row basis.
In given example, open cursor Emp_Cur and Fetch the Emp_Code records and assigned into a local Variable called @mCardNo.
3. Run through the Cursor
For this there is a Cursor function called @@FETCH_STATUs which is a scalar function that works with cursors.
If @@FETCH_STATUS=0 means cursor doesn’t have more records to read otherwise it would be nonzero. Normally we would use this with while loop (While @@FETCH_STATUS=0).

While @@Fetch_Status=0
 Begin
   ----
   --You can write you statements here for update/Insert
   ----

   Fetch Next From Emp_Cur
   Into @mCardNo
 End

Another cursor function is @@CURSOR_ROWS which would returns the number rows in the most recently opened cursor.
4. Close the Cursor.
Once you completed the cursor run through you must close the cursor otherwise when open it again SQL would throw an error.

Close Emp_Cur

5. Deallocate the Cursor
This this for erase the declared cursor from memory as it would consume considerable memory space.

Deallocate Emp_Cur

The full script for Cursor, ( for run this script you must have a table call Employee_Details)

Declare @mCardNo as Varchar(10)

Declare Emp_Cur Cursor For
Select Emp_Code From Employee_Details

Open Emp_Cur
Fetch Next From Emp_Cur
Into @mCardNo

While @@Fetch_Status=0
 Begin
   ----
   --You can write you statements here for update/Insert
   ----

   Fetch Next From Emp_Cur
   Into @mCardNo
 End

Close Emp_Cur
Deallocate Emp_Cur