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

From: Keith Kratochvil (sqlguy.back2u_at_comcast.net)
Date: 03/15/04 

Date: Mon, 15 Mar 2004 15:10:14 -0600

Yes. You can read about table variables within Books Online. 

-- 
Keith
"SStory" <TheStorys@TAKEOUTTHISSPAMBUSTERsofthome.net> wrote in message news:OAvYH7sCEHA.2600@TK2MSFTNGP12.phx.gbl...
> ok.. But doesn't taht mean it is just temporary like any other variable and
> goes away after the proc is called?
> 
> Thanks.
> 
> 
> "Keith Kratochvil" <sqlguy.back2u@comcast.net> wrote in message
> news:O1j9VzsCEHA.3256@TK2MSFTNGP09.phx.gbl...
> That code creates a table variable.
> This functionality is only possible within SQL Server 2000 and higher.
> 
> -- 
> Keith
> 
> 
> "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
> > >
> > >
> >
> >
> 
>

Relevant Pages