Creating a Parameterized Query

  • Article

Just as you can create parameterized views using the View Designer or the language, you can create a parameterized SQL pass-through query.

To create a parameterized query with SQL pass-through

  • Precede a Visual FoxPro parameter with a question mark (?) symbol, and then include the parameter in a SQL string you send with SQLEXEC( ).

    The parameter you supply is evaluated as a Visual FoxPro expression and the value is sent as part of the view's SQL statement. If the evaluation fails, Visual FoxPro prompts for the parameter value.

    Tip   If your parameter is an expression, enclose the parameter expression in parentheses. This ensures the entire expression is evaluated as part of the parameter.

    For example, if you have the customer table from the Testdata database on a remote server, the following code creates a parameterized query that limits the view to those customers whose country matches the value supplied for the ?cCountry parameter:

    ? SQLEXEC(1,'SELECT * FROM customer WHERE customer.country = ?cCountry')

If you want to prompt the user for a parameter value, enclose the parameter expression in quotation marks. For more information on prompting for a parameter value, see Creating Views.

Your ODBC data source doesn't accept parameters in the following locations:

  • In a SELECT fields or tables list.
  • As both expressions in a comparison predicate.
  • As both operands of a binary operator.

An ODBC data source will not accept parameters in the following locations in the WHERE or HAVING clause of a SELECT statement:

  • As both the first and second operands of a BETWEEN predicate.
  • As both the first and third operands of a BETWEEN predicate.
  • As both the expression and the first value of an IN predicate.
  • As the operand of a unary + or - operator.
  • As the argument of a SET function.

Using SQL Server Input/Output Parameters

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

Returning Multiple Result Sets | Creating Outer Joins with Remote Data | Using SQL Pass-Through Technology | Implementing a Client/Server Application | Designing Client/Server Applications | Upsizing Visual FoxPro Databases | Creating Views