[AccessD] Now that the SP_ExecSQL is working

I just discovered that up in the query window I when I print 
I am getting the original zero that I set the variable to. 
It appears that even though I pass the variable in to my 
stored procedure, it is never modified inside of the SP.

It is almost as if a "pass by value" is happening.  However 
my SP definitely has the OUTPUT in the SP definition line, 
my understand being that this forced a pass by reference.

I am lost.

John W. Colby
www.ColbyConsulting.com


jwcolby wrote:
> The next step is for me to get values back from my SP.  I moved the 
> variables into the SP call:
> 
> ALTER PROCEDURE USP_MinMaxPKID
> 
> @TblName varchar(50), @MinPKID int output, @MaxPKID int OUTPUT
> 
> other than that I left the code inside the same.  I did add a print 
> statement AFTER the sp_executesql line to allow me to see that I am 
> getting the values back from sp_executesql
> 
> Print 'MinPK=' + cast(@MinPK as varchar)
> 
> I also copied the entire block to do the MAX() at the same time.
> 
> I am getting the values returned and placed into the output parameters 
> in the SP function definition.  Very Cool! Thanks Asger!  And they are 
> printing correctly to the message screen.
> 
> What I need now is to get them back out of my SP.  Up in the query 
> window I am using for testing this I dimension two variables:
> 
> declare @MinPKID int
> declare @MaxPKID int
> 
> I then call my SP
> 
> exec USP_MinMaxPKID 'vRawAccuzipOut', @MinPKID, @MaxPKID
> 
> I then print the variables in the query window to see if I got anything, 
> using the exact same syntax I used inside of the SP.
> 
> Print 'MinPK=' + cast(@MinPK as varchar)
> 
> Up at the calling query level I get nothing, literally (AFAICT) a null 
> value returned which when appended with the rest of the string to print, 
> causes the whole string to be null and nothing but a CRLF prints.
> 
> So... am I allowed to do this kind of thing in the query window?  
> Dimension a variable, pass it in to a SP which is supposed to return a 
> value, then print the variable on the next line?  I have never tried 
> this before but "it ain't happening".  I DO still see the print 
> statements inside of the SP print the valid values, so I know that the 
> variables are being set inside of the SP, but nothing is "getting back 
> out" of the SP.
> 
> TIA for any assistance.