[AccessD] Stored Procedure return value - simplified

So you are saying that yes, it is required but it really 
doesn't do anything?

And yes, I am laughing.  ;-)  BOL is IMHO as useless as tits 
on the proverbial boar unless you already know this stuff, 
in which case why would you need it?

It is like a grammar book for English.  If you already know 
how to parse a sentence then it will teach you the syntax 
for parsing a sentence, otherwise it will confuse the hell 
out of you.

Unfortunately, many folks who know this stuff REALLY well 
are very quick to point you to BOL.

Oh well.

Thanks for figuring out whatever it is you just figured out. 
  ;-)

John W. Colby
www.ColbyConsulting.com


Asger Blond wrote:
> Don't want to persevere.
> But feeling obliged to present a simplified version not using equation in
> the last argument.
> This equation has puzzled me, as I told you. And I have to admit that I've
> been using it following examples from BOL... Examples that confuses the
> understanding of what's going on... Do I hear laughter?
> So be it and here are the simplified codes without the equation in the last
> argument:
> 
> /* PROC WITHOUT OUTPUT PARAMETERS */
> CREATE PROCEDURE usp_MinMaxPKID @tblName varchar(100)
> AS
> DECLARE @MinPKID int, @MaxPKID int, @sql nvarchar(100);
> SET @sql = N'SELECT @internalMinPK = min(PKID) FROM ' + @tblName;
> EXEC sp_executesql @sql, N'@internalMinPK int OUTPUT', @MinPKID OUTPUT;
> SET @sql = N'SELECT @PKID = max(PKID) FROM ' + @tblName;
> EXEC sp_executesql @sql, N'@PKID int OUTPUT', @MaxPKID OUTPUT;
> SELECT @MinPKID AS MinPKID, @MaxPKID AS MaxPKID;
> GO
> 
> -- Execution of proc:
> EXEC usp_MinMaxPKID 'MyTable'
> GO
> 
> 
> /* PROC WITH OUTPUT PARAMETERS */
> ALTER PROCEDURE usp_MinMaxPKID
> @tblName varchar(100), @MinPKID int OUTPUT, @MaxPKID int OUTPUT
> AS
> DECLARE @sql nvarchar(100);
> SET @sql = N'SELECT @internalMinPK = min(PKID) FROM ' + @tblName;
> EXEC sp_executesql @sql, N'@internalMinPK int OUTPUT', at MinPKID OUTPUT;
> SET @sql = N'SELECT @internalMinPK = max(PKID) FROM ' + @tblName;
> EXEC sp_executesql @sql, N'@internalMinPK int OUTPUT', at MaxPKID OUTPUT;
> GO
> 
> -- Execution of proc:
> DECLARE @min int, @max int
> EXEC usp_MinMaxPKID 'MyTable', @min OUTPUT, @max OUTPUT
> SELECT @min AS [This is minimum PKID], @max AS [This is maximum PKID]
> GO
> 
> 
> Bottom line: The last argument in my original code "@internalMinPK = @MinPK
> OUTPUT" is a redundant statement saying "output @MinPK making it equal to
> the result in @internalMinPK". Simplifying this says "just output the result
> to @MinPK".
> 
> 
> Asger
> 
> -----Oprindelig meddelelse-----
> Fra: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] På vegne af jwcolby
> Sendt: 29. januar 2009 18:44
> Til: Access Developers discussion and problem solving
> Emne: Re: [AccessD] Stored Procedure return value
> 
> Asger,
> 
> Thanks for that explanation.
> 
> So without the final OUTPUT, the syntax is still valid but 
> the value would not be returned?  What then is the point of 
> the OUTPUT in N'@internalMinPK int OUTPUT'?
> 
> And is the final
> 
> @internalMinPK = @MinPK OUTPUT
> 
> not telling the sp_executesql to place a vlue into the 
> control I am passing in, even  without the OUTPUT?  Or would 
> it simply be passing in a value to be used inside of 
> sp_executesql.
> 
> The more I think about this, the more confused I get.
> 
> I think perhaps I am taking things too literally or 
> something.  Someone said that the OUTPUT was the same thing 
> as saying "by reference".  In this syntax you are struggling 
> to teach me it seems that I am saying "by reference" all 
> over the place, when it should only need to be said once.
> 
> John W. Colby
> www.ColbyConsulting.com
> 
>