How to: Create a Parameterized Query
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:
? 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
Tasks
How to: Return Multiple Result Sets
Other Resources
Using SQL Pass-Through Technology
Enhancing Applications Using SQL Pass-Through Technology
Planning Client/Server Applications