Stored Procedure Cursor problem

Question

User809322603 posted

hi friends

Please check the error in my stored procedure

create procedure payments
as
declare @mplan varchar(800),@id int

DECLARE myqry CURSOR FOR
select manuid,memberPlan from manubill
OPEN myqry
FETCH  myqry into @id,@mplan

if (@id =1)
begin
 update manubill set status ='Deactive'
end
close myqry

exec payments

this is giving following error

Msg 16915, Level 16, State 1, Procedure payments, Line 6
A cursor with the name 'myqry' already exists.

Please see the table coding

create table manubill(billid int identity(1,1), manuid int ,memberPlan  varchar(8000), startdate datetime, enddate datetime, plancost float, status varchar(8000)  )

insert into manubill(manuid,memberPlan,startdate,enddate,plancost,status) values(1,'p2','2012-01-26', '2012-02-25', 550,'Active')
insert into manubill(manuid,memberPlan,startdate,enddate,plancost,status) values(1,'p4','2012-02-26', '2012-03-25', 650,'Active')

Answer 1

User581622694 posted

Check following its work fine , it may help you to resolve your error .

http://hamidseta.blogspot.com/2008/11/cursor-in-sql-server.html

Answer 2

User551462331 posted

try changing this line

FETCH  myqry into @id,@mplan

as

FETCH NEXT FROM myqry into @id,@mplan

hope this helps...

Answer 3

User809322603 posted

not solved yet

Answer 4

User522486851 posted

hey change the syntax as follow

 FETCH NEXT FROM  myqry
    INTO @id,@mplan

refer the msdn link here

http://msdn.microsoft.com/en-us/library/ms180169.aspx