Parameter Queries

Queries using PARAMETERS can ask for user specified parameters when the query is run. Parameter variables are declared in the first line of the query using a declaration in the form:

 

PARAMETERS name type;

 

Where name is a legal variable name (no spaces or special characters) and type is a valid data type such as TEXT, INTEGER, etc. Thereafter, the parameter variable can be used in the query as a variable.

 

The project pane will report the number of parameters in a query when the query is highlighted in the project pane.

 

Example

 

This example uses the Order Details table from the Nwind.mdb sample database. Import this table from the database.

 

images\sc_parameter_query_01.gif

 

Use File - Create - Query to create a query and then enter the above text.

 

images\btn_run_query.gif Press the Run button in the project pane to execute the query.

 

When we do so, the Query Parameters dialog launches to allow us to enter values for those parameters used in the query. The query we've written is a simple query that has only one user supplied parameter called Threshold so only this parameter appears in the dialog.

 

images\sc_parameter_query_03.gif

 

The first row in the list of query parameters (if there is more than one) will be highlighted. To enter a value, double click into the Value cell for that parameter, enter the desired value and then press Enter.

 

images\sc_parameter_query_04.gif

 

We've entered the value 10. Press OK to continue with the query.

 

images\sc_parameter_query_05.gif

 

The result is a table that shows all records with a quantity greater than 10.

 

Queries using more than one parameter can be constructed. List multiple parameters separated with commas, for example:

 

PARAMETERS [Beginning Date] DateTime, [Ending Date] DateTime;

 

When more than one parameter has been used the Query Parameters dialog will show each parameter. To specify the values desired for each parameter, double click into the Value cell for each parameter and enter the value desired.

 

Tech tip: a keyboard shortcut to double clicking into a Value cell is to use the down and up keyboard arrows to highlight the desired row in the dialog and then press the F2 key to begin editing the value.

 

See Also

 

Queries

SQL in Manifold System

Data Types

PARAMETERS Declaration