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 14:33:20 -0600

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