1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Cursors In SQL Server

Discussion in 'SQL Server' started by Sagar Jaybhay, Apr 13, 2020.

  1. Sagar Jaybhay

    Sagar Jaybhay New Member

    Joined:
    Jan 28, 2019
    Messages:
    29
    Likes Received:
    17
    Trophy Points:
    3
    Gender:
    Male
    Occupation:
    Sr. Software Developer
    Location:
    Pune
    Home Page:
    Cursors In SQL Server

    In a relational database management system takes into consideration then it would process the data in sets inefficient manner.
    But when you have a need to process the data row by row basis then the cursor is the choice. The cursor is very bad at performance and it should be avoided and also you can replace the cursor with join.

    Different Types of Cursors

    1) Forward only
    2) Static
    3) Keyset
    4) Dynamic

    The cursor is loop through each record one by one so that’s why it’s performance is not good.

    Code:
    declare @empid int
    declare @deptid int
    declare @fullname varchar(200)
    
    declare empcurose cursor for 
    select EmpID,full_name,DepartmentID from Employee
    
    open empcurose
    
    fetch next from empcurose into @empid,@fullname,@deptid
    
    while(@@FETCH_STATUS=0)
    begin
    
    print 'EmpID '+cast(@empid as varchar(10))+ ' Name '+cast(@fullname as varchar(100)) + ' deptid '+cast(@deptid as varchar(100))
    fetch next from empcurose into @empid,@fullname,@deptid
    end
    close empcurose
    deallocate empcurose
    

    cursor in rdbms.png


    Code:
    deallocate empcurose
    
    This line is used to deallocate all resources which are allocated for that cursor.
     
    shabbir likes this.

Share This Page