Billy Pang
tuxedo_man at hotmail.com
Tue Jan 13 14:51:40 CST 2004
Some thoughts: - not sure why you need the @counter <=5 in the while condition. maybe just using "@@FETCH_STATUS = 0" is good enough? - you probably want to use BEGIN-END block right below your WHILE loop; within the BEGIN-END block, put in there what you want to do for each record you retrieve from tblMyB32Updates. Alsol, you need to include the "fetch next from cursortest into @system_no, @cs_no" line in there so that you will move onto the next record. - I don't remember the syntax top of my head but if you are concerned with speed, try using the FAST-FORWARD-READ-ONLY cursor because it is faster than the other cursors HTH Billy >From: "Nicholson, Karen" <knicholson at gpsx.net> >Reply-To: dba-sqlserver at databaseadvisors.com >To: dba-sqlserver at databaseadvisors.com >Subject: RE: [dba-SQLServer]Stored Procedure Mystery >Date: Tue, 13 Jan 2004 15:25:47 -0500 > >I think I am getting it, doing the cursor thing. >still have to add my begin statement fetch next (always >reminds me of a dog ....) and so forth. Think this will >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 > >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 >while (@counter <=5) and (@@fetch_status=0) >close cursortest >deallocate cursortest >GO > >-----Original Message----- >From: Nicholson, Karen [mailto:knicholson at gpsx.net] >Sent: Tuesday, January 13, 2004 2:46 PM >To: dba-sqlserver at databaseadvisors.com >Subject: RE: [dba-SQLServer]Stored Procedure Mystery > > >The values are grabbed from a table that I built. The 279104 is the >system_no >and the '6881.7841' is the cs_no. > >These are three stored procedures that need to be run in succession for all >of the >records in my table, myB32Updates. > >I have Professional SQL Server book open to SQL cursors. Trying to see if >I >can >create a cursor to modify these three lines that I am holding in a stored >procedure. > >-----Original Message----- >From: Billy Pang [mailto:tuxedo_man at hotmail.com] >Sent: Tuesday, January 13, 2004 2:38 PM >To: dba-sqlserver at databaseadvisors.com >Subject: RE: [dba-SQLServer]Stored Procedure Mystery > > >Where are the values grabbed from? A table or from the results of the >sproc? > > Also, what is the signficance of the "exec ap_cs_account_to_change_tran >279104, 'GUARD', '6881.7841'" line? > > > >From: "Nicholson, Karen" <knicholson at gpsx.net> > >Reply-To: dba-sqlserver at databaseadvisors.com > >To: "Dba-Sqlserver (E-mail)" <dba-sqlserver at databaseadvisors.com> > >Subject: [dba-SQLServer]Stored Procedure Mystery > >Date: Tue, 13 Jan 2004 14:08:17 -0500 > > > >Where has everybody been? No messages in like, forever. Did you all hit > >the > >lottery? > > > >Here is the top question of the day!!!! > > > >I have three stored procedures that I need to run in order for each > >account. > > > >exec ap_system_to_b32 279104, '6881.7841', 'GUARD', 'A', 0 > >exec ap_system_to_b32 279104, '6881.7841', 'GUARD', 'C', 0 > >exec ap_cs_account_to_change_tran 279104, 'GUARD', '6881.7841' > > > >Now, I have a file that contains that 6 digit number following the b32, >and > >the corresponding > >'6881.xxxx' number in another file. All other values are constant. > > > >Is there some way (in English) to have these procedures run once with > >values, go to the next line and grab the next values and populate the >line, > >execute, go to the next set of values, etc. > > > >THANKS! > >_______________________________________________ > >dba-SQLServer mailing list > >dba-SQLServer at databaseadvisors.com > >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > >http://www.databaseadvisors.com > > > >_________________________________________________________________ >MSN 8 with e-mail virus protection service: 2 months FREE* >http://join.msn.com/?page=features/virus&pgmarket=en-ca&RU=http%3a%2f%2fjoin >.msn.com%2f%3fpage%3dmisc%2fspecialoffers%26pgmarket%3den-ca > >_______________________________________________ >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 >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > _________________________________________________________________ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963