How to: Create a Parameterized Query

JavaScript Editor js editor     Web development 



Main Page

Just as you can create parameterized views using the Query and View Designers 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 (?), and then include the parameter in a SQL string you send with SQLEXEC( ) Function.

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.

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:

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

If you want to prompt the user for a parameter value, precede the parameter expression with a question mark (?). For more information, see How to: Create Parameterized 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.

For more information, see Using SQL Server Input/Output Parameters.

See Also



JavaScript Editor js editor     Web development