[AccessD] Stored Procedure return value

John,

I did not create a sproc, but I did get the following to work:

	declare @MinPKID  int
	declare @MaxPKID  int
	declare @tbl nvarchar(20)
	declare @sql nvarchar(250)

	set @tbl='dbo.videos'		--table name

	--delete temp table if it exists
	if ( object_id( 'tempdb..#tbl_Var' ) is not null )
		drop table #tbl_Var

	--create the temp table
	Create Table #tbl_Var (
		MinPK int, 
		MaxPK varchar(10))

	--build the SQL string
	set @sql='INSERT INTO #tbl_Var (MinPK,MaxPK)
				(select min(VideoID),max(VideoID) from ' +
@tbl + ')' 

	--execute the sql
	EXEC sp_executesql @sql

	--extract the data from the temp table
	Select @MinPKID =MinPK, at MaxPKID =MaxPK
	From #tbl_Var

	print @MinPKID 
	print @MaxPKID


In a nutshell, the above:
 	- creates a temp table 
	- inserts the output from the dynamic sql into the temp table
	- extracts the contents of the temp table into variables
	

Hope this helps,
Bobby

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, January 27, 2009 8:50 AM
To: Access Developers discussion and problem solving
Subject: Re: [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/>
>>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com