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.
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
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')
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
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.
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.
Very usefull article,thanks
ReplyDeletegr8
ReplyDeleteuse full article thank you,,,,,,,
ReplyDeleteNice1...
ReplyDeletethanks
ReplyDeletevery useful
Nice Article
ReplyDeletedeclare @ID INT
ReplyDeletedeclare @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
you can try Forward Only Cursors .
ReplyDelete