SQL Server Performance Forum – Threads Archive

On Cursor

Hi, I am doing a cursor operation for inserting , every thing is fine but the last row is being inserting twice Wht is hte reason???? Regards
Rajesh

Why do you use Cursor? Cant you use this? Insert into table2(columns)
Select columns from table1 Otherwise post the code you used Madhivanan Failing to plan is Planning to fail

Please post the code you use. Probably there’s a way to get rid of the cursor. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

–exec usp_savenewrides ‘2*13*14*15*16*17′,45,56,’suresh’,’aaaaaaaa’,7 CREATE PROCEDURE usp_SaveNewRides
(
@string varchar(500)
,@Perceived_Exertion float
,@Avg_HR float
,@KeywordsToSearch varchar(255)
,@Notes varchar(8000)
,@RiderID int
)
AS
DECLARE @AuthorID int
BEGIN
DECLARE c1 CURSOR FOR
SELECT value FROM fn_Split(@string, ‘*’) OPEN c1
FETCH NEXT FROM c1
INTO @AuthorID
insert into Avg_Ride_Attr
(
RideID
,Perceived_Exertion
,Avg_HR
,KeywordsToSearch
,Notes
,RiderID
)
values
(
@AuthorID
,@Perceived_Exertion
,@Avg_HR
,@KeywordsToSearch
,@Notes
,@RiderID
) WHILE @@FETCH_STATUS = 0
BEGIN FETCH NEXT FROM c1
INTO @AuthorID
insert into Avg_Ride_Attr
(
RideID
,Perceived_Exertion
,Avg_HR
,KeywordsToSearch
,Notes
,RiderID
)
values
(
@AuthorID
,@Perceived_Exertion
,@Avg_HR
,@KeywordsToSearch
,@Notes
,@RiderID
) END CLOSE c1
DEALLOCATE c1
END
GO

Do you know this one?http://www.sommarskog.se/arrays-in-sql.html —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

You need to brush up your WHILE loop syntax … A loop contains a number of lines between BEGIN and END, and each line is executed after the condition has been found to be true. So "WHILE" shouldn’t be taken literally! Only after the last line in the loop is executed, the condition is checked again, so the loop may be exited. So within any WHILE @@FETCH_STATUS = 0 loop, you must put the FETCH NEXT line after the processing of the values, not before. You don’t seem to notice, but the first row of the cursor is actually being skipped because of the wrong order of the lines. The last row of the cursor is perhaps entered twice because the last FETCH NEXT doesn’t reset the variables?

Instead of Cursor part, try this insert into Avg_Ride_Attr
(
RideID
,Perceived_Exertion
,Avg_HR
,KeywordsToSearch
,Notes
,RiderID
)
SELECT value ,@Perceived_Exertion ,@Avg_HR ,@KeywordsToSearch ,@Notes ,@RiderID FROM fn_Split(@string, ‘*’) Madhivanan Failing to plan is Planning to fail

]]>