[AccessD] Stored Procedure return value

Asger Blond ab-mi at post3.tele.dk
Tue Jan 27 15:19:44 CST 2009


John,

Have a look at sp_executesql, which allows you to send the value of an
output parameter to a variable.

You could create a sp like this:

CREATE PROCEDURE usp_MinMaxPKID @tblName varchar(100)
AS
DECLARE @MinPKID int, @MaxPKID int, @sql nvarchar(100);
SET @sql = N'SELECT @PKID = min(PKID) FROM ' + @tblName;
EXEC sp_executesql @sql, N'@PKID int OUTPUT', @PKID=@MinPKID OUTPUT;
SET @sql = N'SELECT @PKID = max(PKID) FROM ' + @tblName;
EXEC sp_executesql @sql, N'@PKID int OUTPUT', @PKID=@MaxPKID OUTPUT;
SELECT @MinPKID AS MinPKID, @MaxPKID AS MaxPKID;
GO

Then execute the sp like this:

EXEC usp_MinMaxPKID 'MyTable';
GO

HTH
Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af jwcolby
Sendt: 27. januar 2009 19:39
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Stored Procedure return value

I am not going to get into a "dynamic SQL" debate.  Simply 
understand that I run SQL Servers that have exactly and only 
one person accessing them, ME.

I have literally many dozens of databases, each with dozens 
of queries and tables.  I am not going to edit the stored 
procedures in each database I create in order to avoid an 
injection attack that will never come.

In my case it does initially complicate what I want to do 
but it simplifies the big picture.  And once I learn the 
technique, whatever it might be, then it will no longer 
complicate my life.

I have found an article about exactly what I am trying to 
do, but of course it is just complicated enough that I have 
to study it very carefully in order to figure out how the 
heck they are doing it.

Thanks for your example, I will work with it as well.

John W. Colby
www.ColbyConsulting.com


Arthur Fuller wrote:
> Hi John,
> 
> I must confess that I am not a fan of dynamic SQL, and that in your case
it
> complicates things.
> 
> Normally (e.g. static SQL) one would write something like this:
> 
> SET @minID = (SELECT Min(PKID) FROM myTable)
> and similarly for @maxID
> 
> I haven't actually tried doing this in dynamic SQL but I think the same
> thing should work from there too. The trick is the SET part of the
> statement.
> 
> hth,
> Arthur
> 
> On Tue, Jan 27, 2009 at 8:50 AM, jwcolby
<jwcolby at colbyconsulting.com>wrote:
> 
>> 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 <http://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/> <
>> http://www.colbyconsulting.com/>
>>  >>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com






More information about the AccessD mailing list