[dba-SQLServer]Cursor Help!!!

You are my hero.  Thank you so much.  

-----Original Message-----
From: Stoker, Kenneth E [mailto:Kenneth.Stoker at pnl.gov]
Sent: Thursday, January 15, 2004 11:59 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Cursor Help!!!


I believe that it is because your loop is only incrementing your count
variable and fetching the next record.  All the exec statements and the
insert statement occur prior to the while loop and therefore only get
executed once because they are not part of the loop.  If you move your
while (@@fetch_status = 0) Begin lines before the print line, like
below, it should work.

CREATE PROCEDURE dbo.gps_move_to_B32 AS

declare @counter	int,
	 @system_no	int,
	 @cs_no	varchar(10)
declare cursortest	CURSOR
GLOBAL
FOR
	select system_no, cs_no
	from
	tblMyB32Updates
            where  system_no between  279128 AND  279364
            
select @counter = 1
open cursortest
fetch next from cursortest into @system_no, @cs_no

while  (@@fetch_status=0)
begin
	print  convert(varchar, at system_no) + convert(varchar, at cs_no) 

	exec ap_system_to_b32 @system_no, @cs_no, 'GUARD', 'A', 0 

	exec ap_system_to_b32 @system_no, @cs_no, 'GUARD', 'C', 0 

	exec ap_cs_account_to_change_tran @system_no, 'GUARD', @cs_no 
	
	insert into system_event(cs_no,
[date],event_class,comline_no,operator,scheduled_date,server_id,
		cs_event_no)
	values(@cs_no, getdate(), 'A',0,'KN','1/1/1900','A',4625)

	select @counter = @counter +1
	fetch next from cursortest into @system_no, @cs_no
end

close cursortest
deallocate cursortest
GO

Ken Stoker
Technology Commercialization
Information Systems Administrator
PH: (509) 375-3758
FAX: (509) 375-6731
E-mail:  Kenneth.Stoker at pnl.gov 


-----Original Message-----
From: Nicholson, Karen [mailto:knicholson at gpsx.net] 
Sent: Thursday, January 15, 2004 6:57 AM
To: Dba-Sqlserver (E-mail)
Subject: [dba-SQLServer]Cursor Help!!!


This procedure just hits one record, it does not loop
through.  Any ideas why?  This is only my second
cursor routine, thank goodness.  Oh - and the between
statement is correct - all those records are there
in the tblMyB32Updates.


CREATE PROCEDURE dbo.gps_move_to_B32 AS

declare @counter	int,
	 @system_no	int,
	 @cs_no	varchar(10)
declare cursortest	CURSOR
GLOBAL
FOR
	select system_no, cs_no
	from
	tblMyB32Updates
            where  system_no between  279128 AND  279364
            
select @counter = 1
open cursortest
fetch next from cursortest into @system_no, @cs_no
print  convert(varchar, at system_no) + convert(varchar, at cs_no) exec
ap_system_to_b32 @system_no, @cs_no, 'GUARD', 'A', 0 exec
ap_system_to_b32 @system_no, @cs_no, 'GUARD', 'C', 0 exec
ap_cs_account_to_change_tran @system_no, 'GUARD', @cs_no insert into
system_event(cs_no,
[date],event_class,comline_no,operator,scheduled_date,server_id,
cs_event_no)
values(@cs_no, getdate(), 'A',0,'KN','1/1/1900','A',4625)
while  (@@fetch_status=0)
begin
	select @counter = @counter +1
	fetch next from cursortest into @system_no, @cs_no
	end

close cursortest
deallocate cursortest
GO
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com