jwcolby
jwcolby at colbyconsulting.com
Thu Jan 29 12:20:46 CST 2009
Shamil, I indeed "miss that", in fact I never knew that. Once I learned that from Asger's answers, I applied it to my query window and voila. This just goes to show how a little knowledge can be dangerous. This is the equivalent in Basic of: function MyFunc(SomeVar byref) end function X = myfunc(Y byref). We Basic users are so accustomed to declaring the ByRef in the function and just passing in a variable that it "makes no sense" to have to declare it in the calling function. The thing is, I understand down to the machine code language what is going on in the basic code. By declaring a byref I am declaring that a pointer will be passed in to the function. By declaring ByVal I am declaring that the data pointed to by the pointer will be passed in. What is actually going on in a SP? In the SP you are declaring that a pointer is being passed in? If so why do you need to do that again out in the caller? To tell the caller to pass a pointer? Are there even pointers involved at all or is this a completely different construct? Is there some programmatic "boundary" that we are throwing things across and so have to tell the thrower exactly what to throw and the catcher exactly what is being thrown? Anyway, thanks for the reply. John W. Colby www.ColbyConsulting.com Салахетдинов Шамиль wrote: > 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 >> >