[AccessD] Stored Procedure return value

Arthur,

What I still don't understand is how I get the data into the 
variables to return INSIDE of the stored procedure.


For example, I might have a table with the PKID starts at 
~13 million and go to 87 million.  My external process can 
only process 1 million records maximum so I need to grab 1 
million record chunks of data.

I need to get the min(PKID) and max(PKID) and I need to 
return these to code out in Access so that I can then "get 
the next million records" based on the PKID.  I want to do a 
bulk export starting at 13 million to 14 million, then when 
that works, 14 to 15 million etc.

I have the SP written and tested that does that bulk export, 
now all I need is the min(PKID) and Max(PKID) so I can start 
carving up the table.

I have written a usp_MinMaxPKID @tblName varchar, @MinPKID 
int output, @MaxPKID int output

So I have a SP with the values passed in and declared OUTPUT 
(return value to calling procedure).  In the past I have 
written dynamic code something like:

declare @SQL = 'SELECT Min(PKID) as MinPKID, Max(PKID) as 
MaxPKID from dbo.' + @tblName.

exec(@sql)

This had the effect of getting the min and max from the 
table passed into @tblName, but it places the results into 
the results pane in the query window when I execute the SP.

Now I need to somehow place the results into the @MinPKID 
and @MaxPKID.  It is this piece that I am "missing".

Any help (from anyone) on how to do this would be much 
appreciated.

John W. Colby
www.ColbyConsulting.com


Arthur Fuller wrote:
>>From the FE:
> 
> declare a variable called intRowCount and set its value to zero.
> 
>>From the BE's proc:
> 
> Declare a parm called @intRowCount OUTPUT
> 
> Execute the proc, passing your parm in.
> 
> Interrogate the variable intRowCount upon return to your external code.
> 
> That should do it.
> 
> If this is unclear, I could whip up a toy program for you, but I think that
> this ought to be clear enough for you to proceed. If not, say so and I shall
> supply a toy program that demo's this approach.
> 
> A.
> 
> On Fri, Jan 23, 2009 at 5:35 PM, jwcolby <jwcolby at colbyconsulting.com>wrote:
> 
>> can the sp be tested from the query window in SQL Server.
>> IOW, I open a query, EXEC the sp_CountRec and pass in ... what?
>>
>> John W. Colby
>> www.ColbyConsulting.com <http://www.colbyconsulting.com/>
>>