[dba-SQLServer]Stored Procedure Mystery

Nicholson, Karen knicholson at gpsx.net
Tue Jan 13 15:12:50 CST 2004


This is the completed code.  I am doing them in groups of 5 because
this stored procedure is populating tables to go over to a unix system.
I don't want to bring down my data link, so I am doing it in bits and
pieces,
like groups of 5.

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 = 279051*/

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
while (@counter <=5) and (@@fetch_status=0)
begin
	select @counter = @counter +1
	fetch next from cursortest into @system_no, @cs_no
	end
close cursortest
deallocate cursortest
GO

-----Original Message-----
From: Billy Pang [mailto:tuxedo_man at hotmail.com]
Sent: Tuesday, January 13, 2004 3:52 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Stored Procedure Mystery


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%2fjoi
n
>.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

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


More information about the dba-SQLServer mailing list