Hey Andy & Sergey + apologies to Gary for attempting to steal his thread ~ I hope this is an OK thing to do.

I have attempted to follow this thread from start to finish since I am currently dealing with one aspect of this core issue ~ getting data back from a proc: In my case, I am trying to retrieve an actual output that is created from within a stored proc in SQL2K, not simply get a "RETURN" value per se as Gary has been asking.

I have tried the various methods in this thread but must be missing something because I have not succeeded so far. With your forbearance please, may I present my version of this challenge for your help in resolution?

I have a stored proc in SQL which is use to generate and return a string for use as a key. It reads as follows:

*// sql user defined function - top of script
CREATE PROCEDURE dbo.usp_create_char_key (
@tablename varchar(255),
@keyname varchar(255),
@length int,
@newkey varchar(36) OUTPUT)
AS
set nocount on
declare @found bit
declare @newval nvarchar(35)
declare @sql nvarchar (255)

SET @sql = N'IF EXISTS(SELECT ' + @keyname + ' FROM ' + @tablename + N' WHERE ' + @keyname + N' = @P1)'
SET @sql = @sql + N' SELECT @P2=1 ELSE SELECT @P2=0 '
SET @newval=NULL
SET @found=1

WHILE (@found=1)
BEGIN
SET @newval=RIGHT(CAST(NEWID() as varchar(36)), @length)
EXEC dbo.sp_executesql @sql, N'@P1 varchar(255), @P2 bit OUTPUT', @newval, @found OUTPUT
END

set @newkey=@newval

GO
*// sql user defined function - end of script

This function works fine when called from another stored proc in the same SQL2K database as follows:
-- sql stored proc code stuff then we get the calling line below
EXEC dbo.usp_create_char_key 'dbo.babychg', 'babychgid', 12, @babychgid OUTPUT
-- then more sql stored proc code stuff

It correctly returns the desired result to the calling proc in SQL which then uses that value for further processing. So I have attempted to translate this into VFP dialog based upon info in this thread as follows:

*// VFP code used to emulate SQL proc call as above
STORE SQLCONNECT('qcm_Connect1') TO gnConnHandle

PRIVATE pcbabychgid
pcbabychgid = ''

lcSQLQry = "EXEC dbo.usp_create_char_key 'dbo.babychg', 'babychgid', 12, ?pcbabychgid OUTPUT"

w = SQLPREPARE(gnConnHandle, lcSQLQry)

y = SQLEXEC(gnConnHandle)

x = pcbabychgid

*// end of VFP code used to emulate SQL proc call as above

I'm trying to "get" or retieve the string generated by, and output from, the above SQL2K proc in the proc's output parameter @newkey by using this VFP snippet to call that SQL2K proc.
My VFP snippet above runs without error but does not produce any change in my designated output parameter 'pcbabychgid' ~ pcbabychgid stays at ''.

Note: If I include the '@' symbol and use @babychgid or @pcbabychgid as the parameter in the calling prog - even when having PRIVATE babychgid in the heading of the code, I get the error "incorrect syntax error near OUTPUT" and likewise if I use ?@babychgid or ?@pcbabychgid but using ?pcbabychgid has no error but neither does it produce the desired retrieval result.

Since the above failed, based upon data in this thread I have now also tried the alternate approaches as below:
Version a.
lcSQLQry = [{?@pnRetval=CALL dbo.usp_create_char_key 'dbo.babychg', 'babychgid', 12, ?pcbabychgid OUTPUT}]
w = SQLPREPARE(gnConnHandle, lcSQLQry)
y = SQLEXEC(gnConnHandle)
x = pcbabychgid

Version b
lnResult = SQLEXEC(gnConnHandle, [{?@pnRetval=CALL dbo.usp_create_char_key 'dbo.babychg', 'babychgid', 12, ?pcbabychgid OUTPUT}])
x = pcbabychgid

So far, none of these options has produced the desired result. Do you have any clues as to what I am doing wrong? Please advise. Thnx. /psb