[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


Asger Blond wrote:
> John,
> 
> I put a _X after the variable name only to make clear that this variable is
> quite *different* from the variable @MinPK you declare at the beginning of
> your sp.
> I could have used any name. Yes, actually I could have used the name @MinPK,
> but it still would be a variable *different* from the @MinPK you declare at
> the beginning.
> 
> OK, let's use a different name, e.g. @internalMinPK, and have a look at the
> code:
> 
> CREATE PROCEDURE usp_MinMaxPKID	@TblName varchar(50)
> AS
> DECLARE @MinPK int, @SQL nvarchar (4000)
> .
> .
> set @SQL = N'SELECT @internalMinPK = MIN(PKID) FROM ' + @TblName
> 
> exec sp_executesql @SQL, N'@internalMinPK int OUTPUT',
> @internalMinPK = @MinPK OUTPUT
> .
> .
> 
> So, what's going on here?
> You are quite right: In the first argument for sp_executesql you are telling
> sp_executesql that you want to use @internalMinPK inside of its code. And in
> the second argument that this variable is for an int data type and that it
> should return a value.
> 
> The last argument is:
> @internalMinPK = @MinPK OUTPUT
> This part tells sp_executesql to store the value of @internalMinPK into your
> variable @MinPK.
> I admit the syntax may be confusing (at least I was quite so first time I
> saw it):
> When using an equal sign you normally store the value on the right side to
> the left side - here it is the other way round... My understanding (I may be
> wrong, anyone please correct me if so!) is that this "turnaround" is exactly
> what OUTPUT means.
> So I would read the last argument this way: "Take the value of
> @internalMinPK and output it to @MinPK".
> 
> HTH
> Asger
> 
> 
> -----Oprindelig meddelelse-----
> Fra: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] På vegne af jwcolby
> Sendt: 29. januar 2009 15:58
> Til: Access Developers discussion and problem solving
> Emne: Re: [AccessD] Stored Procedure return value
> 
> Asger,
> 
> Thanks for your responses.  This worked for me as well!
> 
> <Rant Mode>
> 
> My problem in all of this is that I very much a novice at 
> SQL Server.  I have been forced to dig in but, like Access, 
> there is just so much to know.  If I sit here just 
> "experimenting" trying this and that I get nowhere.  There 
> is no "debugger" for SPs so it is "try something, run and 
> see".  Worse yet the totality of the feedback is "error near 
> XXX in line YYY", not exactly stellar help.
> 
> It would help me and anyone else following this to know the 
> WHY.  "Change to the following" does in fact make it work, 
> it just doesn't help me in making the NEXT one work without 
> coming back to you.
> 
> WHY do I have to put an _X in there?  What exactly is this 
> thing DOING?
> 
> Let's break this thing down a little bit.
> 
>  > set @SQL = N'SELECT @MinPK = MIN(PKID) FROM ' + @TblName
> 
> I have been doing these kinds of statements, except that I 
> have been just doing an EXEC(@SQL).  When I do it this way, 
> I do NOT use the @ sign in front of the MinPK, and the 
> results get placed into the result tab.
> 
> Now you tell me to place an _X after the variable name, but 
> I do not have a variable named _X.  What is this @MinPK_X, 
> where does it exist?  So I am already off in my head trying 
> to discover what is going on in this line of code, when the 
> answer is in the next line of code.
> 
>  > exec sp_executesql @sql, N'@MinPK_X int OUTPUT', 
> @MinPK_X=@MinPK OUTPUT
> 
> Now we see this _X variable again, in a "definition" kind of 
> statement.  So maybe (I am guessing here) I am telling 
> sp_executesql to create @MinPK_X inside of its code and that 
> it will be a variable that returns a value.
> 
>  >@MinPK_X=@MinPK OUTPUT
> 
> Now (maybe, I am guessing here) I have to pass in my 
> variable (@MinPK) to the variable that sp_executesql has 
> just been told to set up for itself (@MinPK_X).
> 
> I still don't have a clue what the final OUTPUT does.  I 
> defined N'@MinPK_X int OUTPUT', why the final OUTPUT?
> 
> This kind of an explanation for code goes a long way towards 
> helping me do it for myself in the future.  When I read your 
> code, I don't have this "explanation" dialog going on in my 
> head, I have more of a WTF? dialog going on.
> 
> Step back for a moment, forget what you already know about 
> SQL Server and sp_executesql, and then look at these two 
> lines of code:
> 
> set @SQL = N'SELECT @MinPK_X = MIN(PKID) FROM ' + @TblName
> 
> exec sp_executesql @sql, N'@MinPK_X int OUTPUT', 
> @MinPK_X=@MinPK OUTPUT
> 
> Pretty damned cryptic without a whole ton of pre-existing 
> knowledge.
> 
> Now I am all about BOL (NOT!) and I in fact went out and 
> read the help on sp_executesql, but one critical thing to 
> understand about BOL is that it is all about context.  "To 
> understand this, you already have to understand that".  So 
> to say "Go read BOL" is less than helpful.  I have spent 
> hours chasing my tail in BOL... OK now go read THIS... ok 
> now to understand that go read this other... ok now to 
> understand that...
> 
> I am a reasonably smart guy, and if I can read an English 
> language explanation of the WHY then I can pretty much make 
> it work again, and after a while I am comfortable with it.
> 
> <\Rant Mode>
> 
> And I still don't understand the final OUTPUT.
> 
> Asger, I am in fact most appreciative of your help on this. 
>   While I still am not sure of the "why it works", I at 
> least have a solution that I can go use this time.  I may in 
> fact kinda sorta understand enough to make it work in a 
> different circumstance.
> 
> Thanks for your assistance.
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> 
> Asger Blond wrote:
>> John,
>>
>> Change the two statements:
>>
>> set @SQL = N'SELECT @MinPK = MIN(PKID) FROM ' + @TblName
>> exec sp_executesql @sql, N'@MinPK int', @MinPK
>>
>> To:
>>
>> set @SQL = N'SELECT @MinPK_X = MIN(PKID) FROM ' + @TblName
>> exec sp_executesql @sql, N'@MinPK_X int OUTPUT', @MinPK_X=@MinPK OUTPUT
>>
>>
>> Works for me
>>
>> Asger