Using SQL Server Input/Output Parameters

  • Article

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,

"CREATE PROCEDURE sp_test;

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

OUTPUT AS SELECT

@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:

?@parameter_name

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.

    Note

    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

Tasks

How to: Create a Parameterized Query
How to: Return Multiple Result Sets

Other Resources

Using SQL Pass-Through Technology
Enhancing Applications Using SQL Pass-Through Technology
Planning Client/Server Applications
Upsizing Visual FoxPro Databases
Creating Views