Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Cursor In SQL

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 837
    Comment on it

    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.

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: