Inserting rows Using Cursors in Stored Procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » General » Inserting rows Using Cursors in Stored...

Inserting rows Using Cursors in Stored Procedure

Rate Topic

Display Mode

Topic Options

Author

Message

kanaparthi2000

Posted Wednesday, February 20, 2008 7:51 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 27, 2008 1:01 AM
Points: 5, Visits: 28

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

Post #457993

Matt Miller (#4)

Posted Wednesday, February 20, 2008 8:38 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:57 PM
Points: 6,998, Visits: 13,949

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?

Post #458020

Ben Schonenberg

Posted Monday, June 28, 2010 1:08 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 25, 2013 2:20 AM
Points: 17, Visits: 49

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

Post #943690

« Prev Topic | Next Topic »

Permissions