February 20, 2008 at 7:51 am
Hi ALL,
I have written following stored procedure:
CREATE PROC sampleSP AS
Declare @id varchar(15)
begin
declare tbl_loop cursor
for select id from testSP
open tbl_loop
fetch next from tbl_loop into @id
update testSP set flg = 1 where id = @id
insert into testSP2 values(newid(), @id)
while (@@fetch_status = 0)
begin
fetch next from tbl_loop into @id
update testSP set flg = 1 where id = @id
insert into testSP2 values(newid(), @id)
end
end
close tbl_loop
deallocate tbl_loop
The problem here is that after executing the stored procedure, It's inserting 2 duplicate rows for the last record but it should insert only 1 row.
Please Help.
Thanks
Srikanth
February 20, 2008 at 8:38 am
Why use a cursor or a loop at all?
insert testSP2
select newID(), id
from testSP;
update testsp
set flg=1;
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 28, 2010 at 1:08 am
If you insist on using a cursor.
You should check the @@getstatus after the fetch next or, before the fetch next, initialze your variable on NUll and after the fetch next, test if the variable is not null
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply