How to return a value from a server stored procedure

In order to standardize a certain operation and/or enhance security, it is sometimes desirable to invoke a stored procedure on the database server and have that stored procedure return a single value to Visual FoxPro. Typically, returned results are in the form of a VFP cursor, but this incurs additional overhead in your VFP source, because you must store the result set's value into a memory variable then remember to close the cursor.

ODBC supports output parameters that allows you to receive a return value without using a result set. For example, say you want to determine if a customer exists in the database server's Customer table, and if so, what that Customer's status is.

The VFP code would be:

[tt]cOutParam = ""
sqlexec(nHandle, "{CALL sp_chkcustomer ('12345', ?@cOutParam)}") [/tt]

An example MS SQL Server stored procedure would be:

[tt]create procedure sp_chkcustomer
@cCustID char(10),
@cReturn char(1) OUTPUT
AS
declare @cStatus char(1)
select @cStatus=status from customer
where CustomerID = @cCustID
if @@rowcount = 0
set cReturn = "N"
else
set cReturn = @cStatus
return(0)
[/tt]