[AccessD] Stored Procedure return value

BTW what is the difference between SELECT and SET?

John W. Colby
www.ColbyConsulting.com


Asger Blond wrote:
> 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
>>>