[AccessD] Stored Procedure return value

Hey!!! I got the thing to work up in my top level query by 
declaring the variables I passed in to my SP as output, IN 
THE EXEC line.

exec usp_MinMaxPKID 'vRawAccuzipOut', @MinPK output, @MaxPK 
output

OK, a major stumbling block overcome.

LOL.  NOW for the next part.

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