Cursor In SQL

             Cursor is used to iterate within a table. It is a pointer which iterates within a table to fetch rows.

Life cycle of cursor

1)DECLARE

   Firstly you must declare a cursor by using DECLARE keyword.

2)OPEN

   Then you have to open the cursor using OPEN keyword.

3)FETCH

   After cursor is opened rows can be fetched for manipulation.

4)CLOSE

   A cursor should be closed using CLOSE keyword.

5)DEALLOCATE

  DEALLOCATE is used to remove it from memory.

Example

Here, I am printing values of my table using a cursor. I am using "Users" table for this example.

Following is the snippet of User's table -:

Cursor to print values

DECLARE @Id INT 
DECLARE @Name VARCHAR(10) 
DECLARE @UserType INT
DECLARE myCursor CURSOR FOR 
		SELECT * FROM Users
OPEN myCursor 
FETCH NEXT FROM myCursor INTO @Id,@Name,@UserType
WHILE @@FETCH_STATUS = 0 
BEGIN 
PRINT 'ID -:' + CONVERT(VARCHAR(20),@Id) + ' UserName -:' + @Name + ' UserType -:' + CONVERT(VARCHAR(20),@UserType)  
FETCH NEXT FROM myCursor INTO @Id,@Name,@UserType
END	
CLOSE myCursor
DEALLOCATE myCursor

Here, I have declared three variables @Id, @Name and @UserType to store values when a row is fetched. It works like while loop. First all the records from Users table are fetched as result set then row by row iterated and  @@FETCH_STATUS is compared to 0. @@FETCH_STATUS = 0 means fetch is successful. If row fetched successfully then printing the values.

Output

Note

@@FETCH_STATUS = 0 -> Fetch is successful.

@@FETCH_STATUS = 1 -> Fetch failed.

@@FETCH_STATUS = 2 -> Fetch is missing.