Export (0) Print
Expand All

Using SQL Server Input/Output Parameters

Visual Studio 2005

You can use input/output parameters to pass values between Visual FoxPro and SQL Server. Input/output parameters are available only using SQL pass-through; they can't be used in views.

The following table provides an example using input/output parameters to pass values from Visual FoxPro to a SQL Server stored procedure, returning the result to a Visual FoxPro variable.

Using Input/Output Parameters with a SQL Server Stored Procedure
Code Comments

resultCode = SQLExec(connHand,


@mult1 int, @mult2 int, @result int;


@result = @mult1 * @mult2")

Create a stored procedure, sp_test, that multiplies two variables (mult1 and mult2), then stores the resulting amount in the variable result.

outParam = 0

Create a Visual FoxPro variable to receive the output parameter value when it's passed from SQL Server to Visual FoxPro.

resultCode = SQLExec(connHand, ;

"{CALL sp_test (2, 4, ?@outParam)}")

Execute the SQL Server stored procedure, passing the values '2' and '4' to be multiplied together in the stored procedure.

? "outParam =", outParam && the value is 8

Display the value of the output parameter.

Defining Parameters

The syntax for output parameters is:


When you implement input/output parameters, define the Visual FoxPro variables you want to include in your SQL pass-through command before you use the variables in the SQL statement. To successfully send and receive information with input/output parameters, you must define:

  • A stored procedure parameter, with an output type, that returns a value.

    For example, if your stored procedure parameter is @result, you must assign an output type, such as int, to @result, and you must assign a value to @result.

  • An output parameter (@parameter_name) expression that evaluates to an existing Visual FoxPro variable.

    For example, if your output parameter expression is ?@outParam, your application must have defined the Visual FoxPro variable outParam.


    If you don't use an output parameter, either in Visual FoxPro or in the stored procedure, or you don't define a Visual FoxPro variable to receive the return value, the Visual FoxPro parameter value will not change.

Converting Data Types

Visual FoxPro converts returned variable values using the following rules:

  • Floating point data type (N, F, B) variables are converted to N.

  • The display size is set to 20.

  • The decimal setting is set to the current session setting. The decimal setting affects only the default display format, and doesn't affect the decimal precision.

  • Date and time variables (D, T) are converted to time (T) variables.

You can't use Memo, General, Picture, or NULL data types in input/output parameters.

If your application uses cursor fields as parameters, Visual FoxPro will attempt to convert the result back to the original field data type.

Returning Parameter Values

Input/output parameters are available only after the last result set of a statement has been fetched. This means that input/output values are returned to Visual FoxPro only after:

If your SQLEXEC( ) statement requests multiple result sets, the output parameters are only guaranteed to be available after the last result set has been fetched from the data source.

See Also

Community Additions

© 2014 Microsoft