Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 error in cursor

Author  Topic 

saurabhjj
Starting Member

13 Posts

Posted - 2007-11-12 : 09:11:54
hi,
i am writing a cursor where i am selecting 2 fields(both numberic), adding them up and inserting the result along with other parameters into a third table,

following is my cursor

ALTER PROCEDURE dbo.forecast
AS

declare @ins1 cursor

set @ins1=cursor FAST_FORWARD
for select tem.amount,tem1.amount,tem1.heads from tem,tem1 where tem.datsetcode='itchotels001' and tem.heads=tem1.heads

declare @heads1 varchar(50)
declare @amount money
declare @amount1 money

declare @amt money

open @ins1

fetch next from @ins1 into @amount,@amount1,@heads1

while @@FETCH_STATUS = 0

begin

set @amt=@amount1-@amount

insert into chng values ('itchotels001',@heads1,@amt)
fetch next from ins1 into @amount,@amount1,@heads1

end

close @ins1

deallocate @ins1

GO

tem,tem1 and chng are table names

in this cursor the system shows an error near 'Go'

can any one please help me me out

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-12 : 09:32:03
cursors are not variables that need prefixed with an @. here is the online version of Books Online for the DECLARE CURSOR in case you have not YET install BOL...
http://msdn2.microsoft.com/en-us/library/ms180169.aspx
Go to Top of Page

saurabhjj
Starting Member

13 Posts

Posted - 2007-11-12 : 09:40:50
thnx,

another problem has now emerged, the loop is not working

insertion takes place only for the first time, can u please help again
Go to Top of Page

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-12 : 09:55:54
All you do here can be replace with a simple INSERT .. SELECT
Just one statement

insert into chng select 'itchotels001',tem1.heads,tem1.amount-tem.amount
from tem,tem1 where tem.datsetcode='itchotels001' and tem.heads=tem1.heads
Go to Top of Page
   

- Advertisement -