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

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 325
    Comment on it

    A cursor is a database object that can be created at runtime. It is used to get data  from a result set line by line(or row by row) rather than executing  all the rows in the result set at a single time like an SQL-transaction. Generally, we use it when we want to update a database record in line by line fashion.

     

    We have a two types of cursors:
    1. Forward only:- This is a default cursor type. This cursor is used when we need to go in a forward direction from its starting point.


    2. Scroll cursor:- This cursor is used  when we want to move forward, come backward, go to a particular line or position in the result set. This cursor will have different fetching options like FIRST, LAST, NEXT, PRIOR, RELATIVE, ABSOLUTE to retrieve the data.  

     


     Cursor scope


    We have a two types of scope in which cursor can be define:
    1. Local:- when the cursor can be accessed within the stored procedure or trigger in which it is declared.


    2. Global:- When the cursor can be accessed by any outside stored procedure or trigger.

     


    Cursor behaviour


    Cursor can behave in two ways:
    1. Static:- In this behavior, we have  to create a temporary copy of a data on which the cursor work so that any change made in the data value will not reflect on the original data value.


    2. Dynamic:- In this behavior cursor will work on the original value of data so that any change made in the data value will reflect on the original value of data.

     

    Syntax for declaring a cursor:

    DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
         [ FORWARD_ONLY | SCROLL ] 
         [ STATIC | DYNAMIC | FAST_FORWARD ] 
         [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
         [ TYPE_WARNING ] 
         FOR select_statement 
    OPEN cursor_name
    FETCH NEXT FROM cursor_name
    [;]
    

     

    Steps to create a cursor are as follows:
    1. Define a cursor
    2. Open the cursor
    3. Fetch rows from the result set
    4. Close the cursor
    5. Deallocate the cursor

     

    Cursor Example:

    DECLARE @UserId int
    DECLARE @UserFullName varchar(100)
    DECLARE UserDetailsCursor CURSOR FOR
    SELECT UserId , FirstName + SPACE(1)+ LastName FROM User
    WHERE UserId > 4;
    OPEN UserDetailsCursor 
    FETCH NEXT FROM UserDetailsCursor INTO @UserId , @UserFullName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT CAST(@UserId as varchar(20))+ ' :- ' + @UserFullName
        FETCH NEXT FROM UserDetailsCursor INTO @UserId , @UserFullName
    END
    CLOSE UserDetailsCursor 
    DEALLOCATE UserDetailsCursor 
    

     

 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: