Re: get some records and then call a stored proc once for each??

From: SStory (TheStorys_at_TAKEOUTTHISSPAMBUSTERsofthome.net)
Date: 03/16/04 

Date: Tue, 16 Mar 2004 09:55:03 -0600

Basically here is the scenario.

This is a job site.
Have jobs at location A.
People apply and get assigned.
They do a good job and complete it or not.
If they don't complete it they have a bad mark.

The future recurring jobs that get created at the same location for a
different date need to be able to find last good rep and assign.

I have an AssignRep Stored proc.

during create jobs, on my ASP.NET page I let the ADMIN specifiy what, when,
where and click a button. A dataset it created holding all supposed new job
location and date info.
They see a preview of what they are doing and then if they click add I go
through those few jobs one at a time calling a CreateJob stored proc to
create that job.

This is the proc I am modifying.
It now tries to create a job, once created, creates a table variable and
finds the most recent TOP 30 reps who completed the job in this location in
the past 60 days. So the table variable will hold a max of 30 reps and
probably like 8.

Then, maybe the new job needs 2 people or more, if just one I take the top
person and call a stored procedure that assigns that person to the job--yes
could have added the assign rep to job code in there but seemed dumb when I
have a working stored proc to do that.

But if there are more than one needed for this location, i.e. I need 2
people to go to Store X on the 18th or 5 people for that matter for job 123
then I need to loop through and get the last 2 or 5 good reps and assign
them to it.

So I don't think we are talking major overload on any system to do this.
Just a few records actually. I think I have the right code now just
debugging it.

Thanks,

Shane

"Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
news:OpL0iuxCEHA.1484@TK2MSFTNGP12.phx.gbl...
> This is actually probably worse than a cursor, depending on the size of
the
> table. I would suggest you use a cursor if you have to loop through a set
> of rows and do some operation that cannot be achieved using set based
logic.
> Cursors are not horrible, if you have to use them, as long as you choose
the
> right kind of cursor (fast forward is usually acceptable for a temp table)
>
> Of course if it can be rewritten in a single statement, that would be best
> :)
>
> --
> -------------------------------------------------------------------------- 

--
> -----------
> Louis Davidson (drsql@hotmail.com)
> Compass Technology Management
>
> Pro SQL Server 2000 Database Design
> http://www.apress.com/book/bookDisplay.html?bID=266
>
> Note: Please reply to the newsgroups only unless you are
> interested in consulting services.  All other replies will be ignored :)
>
> "SStory" <TheStorys@TAKEOUTTHISSPAMBUSTERsofthome.net> wrote in message
> news:ud6gLTsCEHA.3132@TK2MSFTNGP11.phx.gbl...
> > Keith,
> >
> > I found this code and it seems to work well with no cursor.
> > http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=529
> >
> > one question though
> >
> > Does  the following
> >
> > declare @Import table
> >   (
> >     rownum int IDENTITY (1, 1) Primary key NOT NULL ,
> >     Email nvarchar(255)
> >    )
> > declare a temporary table?
> >
> > I have used #temp
> > but haven't seen this done before. I am assuming it is just like an in
> > memory temporary table or something.
> >
> > I have taken this code and I hope solved the issue I had.
> >
> > Thanks,
> >
> > Shane
> > "Keith Kratochvil" <sqlguy.back2u@comcast.net> wrote in message
> > news:%23%233I5crCEHA.1340@TK2MSFTNGP12.phx.gbl...
> > I am not exactly sure what you are trying to do.  You say that you have
a
> > stored procedure that "gets some params."  Stored procedures usually
> accept
> > parameters.
> >
> > I assume that you know how many (n) rows to retrieve based on some
> business
> > logic, so I will not focus on that either.
> >
> > (1) instead of TOP, perhaps you could use SET ROWCOUNT as in the
following
> > example:
> > (2) it sounds as though you may need to cursor through your temp
> > table...then again there might be a better way.  I don't understand the
> > overall process (as well as other options that you might have) so I will
> > simply point you in this direction.
> > (3) Probably, but we don't have enough information to tell you what it
is.
> >
> >
> > the rowcount example that I mentioned within (1):
> >
> > use pubs
> > go
> > create proc foo
> > @cnt int
> > as
> > set rowcount @cnt
> > select * from authors ORDER BY au_lname DESC
> > go
> >
> > exec foo 3
> > exec foo 5
> > exec foo 2
> > exec foo 0
> >
> > go
> >
> > drop proc foo
> >
> > -- 
> > Keith
> >
> >
> > "SStory" <TheStorys@TAKEOUTTHISSPAMBUSTERsofthome.net> wrote in message
> > news:uQgw$QrCEHA.688@tk2msftngp13.phx.gbl...
> > > I have a stored proc that gets some params.
> > >
> > > Based on those I INSERT 1 record.
> > >
> > > I need to then take that one record, and using some select/joins, I
need
> > to
> > > find the TOP n
> > > most recent employees meeting certain specs.  Then I need to take
those
> > that
> > > are returned--usually 1-5 records and call another stored proc once
for
> > each
> > > of them and if #employees needed < # successful calls to this other
> stored
> > > proc I need to UPDATE the table for this job.
> > >
> > > I can insert the record, use @@identity to get the one inserted,
> > > use a joined select to get the records I need, DESC by date to get
most
> > > recent.
> > >
> > > 2 problems
> > >
> > > 1.) Is it not possible to use the TOP keyword with a variable, like
TOP
> > > @NumNeeded?
> > > I need to do this because one job may need 1 employee but could need
> more
> > > than one 1-5 and at such case I want to find the people who did it
well
> > last
> > > month and assign them to number of positions open for that job.
> > >
> > > I get errors trying to use TOP this way. Is there another?
> > > Also, should I dump that into a temp table in order to call the other
> > stored
> > > proc once for each row returned?
> > >
> > > 2.)  How can I call another stored proc one time for each record in
that
> > > temp table or whereever?
> > >
> > > 3.) Is there a better way?
> > >
> > > Thanks,
> > >
> > > Shane
> > >
> > >
> >
> >
>
>
Loading