[AccessD] Stored Procedure return value - simplified

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