[AccessD] Stored Procedure return value

Thanks Martin.

Given that it is just me and my DB(s), and I am on the other 
side of the pond, I routinely change the nvarchar to varchar 
etc.


John W. Colby
www.ColbyConsulting.com


Martin Reid wrote:
> John
> 
> http://databases.aspfaq.com/general/why-do-some-sql-strings-have-an-n-prefix.html
> 
> Martin
> 
> 
> Martin WP Reid
> Information Services
> Queen's University
> Riddel Hall
> 185 Stranmillis Road
> Belfast
> BT9 5EE
> Tel : 02890974465
> Email : mwp.reid at qub.ac.uk
> ________________________________________
> From: accessd-bounces at databaseadvisors.com [accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby [jwcolby at colbyconsulting.com]
> Sent: 28 January 2009 17:08
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Stored Procedure return value
> 
> Asger,
> 
> Thanks for the response.  So that I can actually understand
> what I am doing, I have a couple of questions:
> 
>  > SET @sql = N'SELECT @PKID = min(PKID) FROM ' + @tblName;
> 
> What does the N' do?
> 
>  > EXEC sp_executesql @sql, N'@PKID int OUTPUT',
> @PKID=@MinPKID OUTPUT;
> 
> is this the equivalent of:
> 
> DECLARE @PKID int OUTPUT
> EXEC sp_executesql @sql, @PKID
> 
> Your code looks like perhaps it is dimensioning variables in
> the same line it is using them or something.  Needless to
> say I am not there yet so understanding (and thus modifying
> to my needs) the code is difficult.
> 
> 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
>>>>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com