[dba-SQLServer]RE: Convert VBA to SQL pointers

Nicholson, Karen knicholson at gpsx.net
Thu Oct 2 16:09:05 CDT 2003

Here you go!  A simple cursor stored procedure to number records in a table
sequentially in the site_no field or whatever I called it.  I put all kinds
of English in here for how the cursor is thinking.  Hope this helps.  It
took me a while to research this.....  and it WORKED!!!!!


/*First, declare the cursor and the dataset unique records that you need to
loop through*/

declare mycursor CURSOR for
select id,
from tblamerican

/* Now we can open the cursor*/

open mycursor

/*Declare two like fields for comparison of unique row*/
declare @tmp_site_id int,
              @siteno int

/*Get your first row*/
fetch next from mycursor into @tmp_site_id,
/*Continue looping through until the end of the recordset*/

while @@fetch_status = 0

/* My goal is to number my site nos, starting with the next site number in
my tblLastSiteNo table */

update tblamerican
set siteno=tblLastSiteNo.site_no+1
/* See this next line, where we marry the two raw tables and then the cursor

where tblAmerican.id = @tmp_site_id and

/*Now I am updating that tblLastSite before I leave this row*/

update tblLastSiteNo
set site_no= site_no+1

/*Continue on my merry way through the end*/

fetch next from mycursor into @tmp_site_id,

/*Clean House*/
close mycursor
deallocate mycursor

-----Original Message-----
From: David Emerson [mailto:davide at dalyn.co.nz]
Sent: Thursday, October 02, 2003 3:35 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]RE: Convert VBA to SQL pointers

Thanks - I would appreciate that.


At 2/10/2003, you wrote:
>I posted a cursor question on Monday or Tuesday.  I have since studied the
>world of cursors.  They should not be used, unless in cases such as these
>where you need to move through each row like we did in the old days.  I am
>almost done with my cursor hell, and think by tomorrow I will have it
>working.  I have my code documented pretty well, so I will e it to you when
>I am done.
>-----Original Message-----
>From: David Emerson [mailto:davide at dalyn.co.nz]
>Sent: Thursday, October 02, 2003 3:19 PM
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer]RE: Convert VBA to SQL pointers
>Thanks David.  I think the key word is cursor.  This seems to be the SQL
>equivalent of a recordset.  I will investigate this further.
>At 2/10/2003, you wrote:
> >I would say yes, definitely.  There are a few tools in sqlserver that
> >help you, but I didn't read through your code enough to give any concrete
> >proposals.  You can avoid moving any data over the wire to the front end
> >using stored procedures.  I would modularize the code here to have one
> >calling procedure that perhaps uses a cursor or table udf to identify the
> >records you want to act on, then call sub procedures to insert the
> >you want, passing appropriate parameters.  You could have one sub
> >per insert, thereby making your code a bit easier to manage.
> >
> >You could also look at triggers to see if they can be any use here.  I am
> >not super experienced, but I have read that triggers are sort of
> >db technology, and may be on their way out, so you might not want to go
> >that road.  I have seen from my own experience that triggers are trickier
> >get right.
> >
> >At any rate, I don't think any of the code you posted needs to be done on
> >the front end.
> >
> >hth.  D. Lewis
> >
> >
> > > Message: 2
> > > Date: Thu, 02 Oct 2003 10:39:17 +1200
> > > From: David Emerson <davide at dalyn.co.nz>
> > > Subject: [dba-SQLServer]Convert VBA to SQL pointers
> > > To: dba-SQLServer at databaseadvisors.com
> > > Message-ID: < at mail.dalyn.co.nz>
> > > Content-Type: text/plain; format=flowed; charset=us-ascii
> > >
> > > Group,
> > >
> > > AXP FE, SQL2000 BE.
> > >
> > > I am looking for pointers at this stage as how I can do the
> > > task.  I have a
> > > vba procedure which runs on the click of a button (most of it
> > > is replicated
> > > below).  As it is currently dealing with over 2000 meters it
> > > takes a while
> > > to run (because it is going back and forth between Access and SQL).
> > >
> > > Basically what it does is loop through one recordset of
> > > meters, looks up
> > > values from other related tables, then adds records to a
> > > couple of other
> > > tables.  It is things like looping through a recordset, and
> > > checking that
> > > records don't exist before creating new records that I am unsure of.
> > >
> > > Is there a way to write the same thing in SQL so that it can
> > > all be run in
> > > the BE?
> > >
> >_______________________________________________
> >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
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com

dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com

More information about the dba-SQLServer mailing list