[AccessD] Now that the SP_ExecSQL is working

John,

You seems to have missed to specify OUTPUT keyword (or just IUT for brevity) to your output parameters when calling SP - it should be:

exec USP_MinMaxPKID 'vRawAccuzipOut', @MinPKID OUTPUT, @MaxPKID OUTPUT

Here is a sample code, which works well here:

if exists (select * from dbo.sysobjects 
where id = object_id(N'[dbo].[usp_TestOutputParams]') and 
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_TestOutputParams]
GO

CREATE Procedure [usp_TestOutputParams]
(
  @TblName varchar(50), 
  @MinPKID int output, 
  @MaxPKID int output
)
As
 Begin
    set @MinPKID = 1
    set @MaxPKID = 999 
 end
GO

declare  
  @testMinPKID int, 
  @testMaxPKID int

exec usp_TestOutputParams
   'TEST', 
   @testMinPKID out,
   @testMaxPKID out

print @testMinPKID
print @testMaxPKID
GO

Thank you.

--
Shamil

-----Original Message-----
From: jwcolby <jwcolby at colbyconsulting.com>
To: Access Developers discussion and problem solving<accessd at databaseadvisors.com>
Date: Thu, 29 Jan 2009 11:10:21 -0500
Subject: Re: [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.
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>