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
          begin
          print '@@FETCH_STATUS'+convert(varchar,@@FETCH_STATUS)
          -- you will write your statements here
          fetch next
          From @Cursor_name into @ID
          end
step6: Close cursor
          close @Cursor_name
step7: Deallocate Cursor
         deallocate @Cursor_name

Example::            
         for your better understanding we will see a example of cursor you create a temp table and fill some data in it.by 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
       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
       end
       close @getEmployee
       deallocate @getEmployee
       select * from #Employee
       select * from #Employee_bk
SUMMERY ::

        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.

8 comments:

  1. Very usefull article,thanks

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

    ReplyDelete
  3. thanks
    very useful

    ReplyDelete
  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
    begin try
    insert into Persons2 values(@ID,@lastname,@fname)
    print 'Inserted'
    end try
    begin catch
    print 'not inseted'
    end catch
    end
    close @copy
    deallocate @copy



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

    ReplyDelete

if you have any doubt any suggestions do comment