[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