Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Inserting rows Using Cursors in Stored Procedure Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2008 7:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, February 20, 2008 8:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 7,115, Visits: 14,983
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
Posted Monday, June 28, 2010 1:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, January 3, 2014 2:01 PM
Points: 21, Visits: 55
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 »

Add to briefcase

Permissions Expand / Collapse