Sign In| Join
Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Dec 30, 2011 10:13 AM by Sankalpa
Member
48 Points
84 Posts
Dec 30, 2011 08:19 AM|LINK
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')
Participant
1299 Points
245 Posts
Dec 30, 2011 08:25 AM|LINK
Check following its work fine , it may help you to resolve your error .
http://hamidseta.blogspot.com/2008/11/cursor-in-sql-server.html
All-Star
34401 Points
5529 Posts
Dec 30, 2011 08:30 AM|LINK
try changing this line
FETCH myqry into @id,@mplan
as
FETCH NEXT FROM myqry into @id,@mplan
hope this helps...
Dec 30, 2011 10:01 AM|LINK
not solved yet
Contributor
2134 Points
494 Posts
Dec 30, 2011 10:13 AM|LINK
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
yogeshweb
Member
48 Points
84 Posts
Stored Procedure Cursor problem
Dec 30, 2011 08:19 AM|LINK
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')
setahamid
Participant
1299 Points
245 Posts
Re: Stored Procedure Cursor problem
Dec 30, 2011 08:25 AM|LINK
Check following its work fine , it may help you to resolve your error .
http://hamidseta.blogspot.com/2008/11/cursor-in-sql-server.html
Hamid Seta
http://www.hamidseta.blogspot.com/
Remember to click “Mark as Answer” on the post, if it helps you.
kedarrkulkar...
All-Star
34401 Points
5529 Posts
Re: Stored Procedure Cursor problem
Dec 30, 2011 08:30 AM|LINK
try changing this line
FETCH myqry into @id,@mplan
as
FETCH NEXT FROM myqry into @id,@mplan
hope this helps...
KK
Please mark as Answer if post helps in resolving your issue
My Site
yogeshweb
Member
48 Points
84 Posts
Re: Stored Procedure Cursor problem
Dec 30, 2011 10:01 AM|LINK
not solved yet
Sankalpa
Contributor
2134 Points
494 Posts
Re: Stored Procedure Cursor problem
Dec 30, 2011 10:13 AM|LINK
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