Wednesday, 29 May 2013

example of Cursor in sql server

Simple example of Cursor.

Hi ,
          In this article we will see a how to use a cursor with some simple example. check a cursor indetails in my post Cursor Indetails

firstly we will see what is syntax of cursor in sql server.
Systax of Cursor::

step1: declaration
          declare @Cursor_name Cursor
step2: set set  
          set @Cursor_name = Cursor for
         Select  id From table_name
         --( You use your query statement)
step3: open cursor 
         open @Cursor_name
step4: Fetch Cursor
         fetch next
         From @Cursor_name into @ID
step5: process cursor 
          while @@FETCH_STATUS = 0
          print '@@FETCH_STATUS'+convert(varchar,@@FETCH_STATUS)
          -- you will write your statements here
          fetch next
          From @Cursor_name into @ID
step6: Close cursor
          close @Cursor_name
step7: Deallocate Cursor
         deallocate @Cursor_name

         for your better understanding we will see a example of cursor you create a temp table and fill some data in using below statement.
        CREATE TABLE#Employee (ID int,emp_name varchar(50))
       INSERT INTO #Employee (ID,emp_name) VALUES(1,'anukrushna')
       INSERT INTO #Employee (ID,emp_name) VALUES(2,'krushna')
       INSERT INTO #Employee (ID,emp_name) VALUES(3,'pranav')
       INSERT INTO #Employee (ID,emp_name) VALUES(4,'aditya')
       INSERT INTO #Employee (ID,emp_name) VALUES(5,'harshal')
       INSERT INTO #Employee (ID,emp_name) VALUES(6,'sandip')
       INSERT INTO #Employee (ID,emp_name) VALUES(7,'ajit')

       Create one more table.
                CREATE TABLE
#Employee_bk (ID int,emp_name varchar(50))

           Now we can write a cursor for fetch records from a #Employee table and fill it in #Employee back up table. Check a below query for achive this .
       declare @ID int
       declare @emp_name varchar(50)
       declare @getEmployee CURSOR
       set @getEmployee = CURSOR for
       select ID,emp_name
       from #Employee
       open @getEmployee
       fetch next
       from @getEmployee into @ID,@emp_name
       while @@FETCH_STATUS = 0
       begin try
       insert into #Employee_bk (ID,emp_name)values (@ID,@emp_name+' backup')
       print Convert(varchar, @ID) +' Inserted'
       print @emp_name+' Inserted'
       end try
       begin catch
       print 'Error in insert' +Convert(varchar, @ID)
       print 'Error in insert' +@emp_name
       end catch
       fetch next
       from @getEmployee into @ID,@emp_name
       close @getEmployee
       deallocate @getEmployee
       select * from #Employee
       select * from #Employee_bk

        In this article I try to explain how use cursor in sql server. I hope it is helpfull to you.I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.


  1. Very usefull article,thanks

  2. use full article thank you,,,,,,,

  3. thanks
    very useful

  4. declare @ID INT
    declare @lastname VARCHAR(255)
    declare @fname VARCHAR(255)
    declare @copy CURSOR
    set @copy = CURSOR for
    select personID,LastName,FirstName FROM Persons

    open @copy
    fetch next from @copy into @ID ,@lastname,@fname
    while @@FETCH_STATUS = 0
    begin try
    insert into Persons2 values(@ID,@lastname,@fname)
    print 'Inserted'
    end try
    begin catch
    print 'not inseted'
    end catch
    close @copy
    deallocate @copy

    this is my code .it is taking to long to execute

  5. you can try Forward Only Cursors .