First for the below sample this is stored procedure (create in Northwind database):
-- MS SQL Server SP:
CREATE PROCEDURE usp_test
@country varchar(10), @customers int output AS
select * from customers where country = @country
set @customers = @@rowcount
if ( @customers > 10 )
return 33
else
return 22
--- FoxPro:
#DEFINE adInteger 3
#DEFINE adCurrency 6
#DEFINE adDate 7
#DEFINE adBoolean 11
#DEFINE adChar 129
#DEFINE adNumeric 131
#DEFINE adVarChar 200
#DEFINE adDBTimeStamp 135
#DEFINE adParamInput 1
#DEFINE adParamOutput 2
#define adParamReturnValue 4
#DEFINE adCmdStoredProc 4
clear
Local cn As ADODB.Connection,;
cmd As ADODB.Command,;
rs As ADODB.Recordset
cn = CreateObject('ADODB.Connection')
cmd = CreateObject('ADODB.Command')
rs = CreateObject('ADODB.Recordset')
cn.ConnectionString = "Provider = SQLOLEDB;"+;
"Server=servername;Database=Northwind;Trusted_Connection=yes"
cn.Open
cmd.ActiveConnection = cn
* Way 1
cmd.CommandText = "usp_test"
cmd.CommandType = 4 && adCmdStoredProc
* Set up parameters
retParm = cmd.CreateParameter("Return", adInteger, adParamReturnValue)
inParm = cmd.CreateParameter("Input", adVarChar, adParamInput,10)
outParm = cmd.CreateParameter("Output", adInteger, adParamOutput)
cmd.Parameters.Append(retParm)
cmd.Parameters.Append(inParm)
cmd.Parameters.Append(outParm)
lcCountry = Trim(InputBox("Enter country:"))
inParm.Value = m.lcCountry
* Way 2
*!* cmd.CommandText = "usp_test"
*!* cmd.CommandType = 4 && adCmdStoredProc
*!* * Set up parameters asking backend
*!* cmd.Parameters.Refresh
*!*
*!* lcCountry = Trim(InputBox("Enter country:"))
*!* cmd.Parameters("@country").Value = m.lcCountry
* Way 3
*!* cmd.CommandText = "{? = Call usp_Test(?,?)}"
*!* cmd.CommandType = 1 && text
*!* * Set up parameters
*!* retParm = cmd.CreateParameter("Return", adInteger, adParamReturnValue)
*!* inParm = cmd.CreateParameter("Input", adVarChar, adParamInput,10)
*!* outParm = cmd.CreateParameter("Output", adInteger, adParamOutput)
*!* cmd.Parameters.Append(retParm)
*!* cmd.Parameters.Append(inParm)
*!* cmd.Parameters.Append(outParm)
*!*
*!* lcCountry = Trim(InputBox("Enter country:"))
*!* inParm.Value = m.lcCountry
* Execute command
rs = cmd.Execute
* Show result
DO While !rs.EOF
? rs.Fields(0).Value
rs.MoveNext
enddo
* Need to close recordset before getting return
* and output parameters.
rs.Close
* check return values - 1 output and 1 return code
* For Way 1 and Way 3
? "Return code: ", Cmd.Parameters("Return").Value && or Parameters(0)
? "Total rows: ", Cmd.Parameters("OutPut").Value && or Parameters(2)
* For Way 2
*!* ? "Return code: ", Cmd.Parameters(0).Value && or Parameters(0)
*!* ? "Total rows: ", Cmd.Parameters("@customers").Value && or Parameters(2)
cn.Close
Please note that return value is always the first parameter -Parameters(0)-.
In way 1 and 3 parameters append order exactly matches SPs definition (positional) - Parameters(0)=return value, Parameters(1) = first parameter, Parameters(2) = second parameter and so on. Name of the parameters has no importance.
In way 2 instead of appending parameters we 'ask' it to the stored procedure (note that this would cause a trip to server) and use named parameters (names match to that of SP parameter names) OR you might use ordinals - Parameters(0),Parameters(1)...
Cetin Basoz (UT forum message #1057068)