Two methods for passing parameters to a query

ONLINE HELP
WINDEV, WEBDEV AND WINDEV MOBILE

Version:

Home | Sign in | English

This content has been translated automatically. Click here to view the French version.

Help / Editors / Query editor / Query with parameters

Overview
Using the . notation
Syntax to use
Using HExecuteQuery to pass parameters to a query
Syntax to use
Avoid specifying all the parameters
Unspecified query parameters

WINDEV

WEBDEV

WINDEV Mobile

Others

See also

Query with parameters

Two methods for passing parameters to a query

Overview

Two methods can be used to pass parameters to a query with parameters used in a window or in a control (List Box, Combo Box or Table control for example):

use <Query name>.<Parameter name>, then HExecuteQuery.
pass the parameters in HExecuteQuery.

This help page presents the operating mode and the benefits of each one of these methods.

Note If the parameterized query is used in a report, the way parameters are passed varies. For more details, see How to use a query with parameters in a report.

Using the <Query name>.<Parameter name> notation

Syntax to use

To pass parameters to a query, all the query parameters can be specified to the HFSQL engine before running the query. This method allows you to specify the parameters in any order (useful for the queries that require several parameters).

The following syntax must be used:

<Query name>.<Name of parameter 1> = <Value of parameter 1>
<Query name>.<Name of parameter 2> = <Value of parameter 2>
...
<Query name>.<Name of parameter N> = <Value of parameter N>

<Result> = HExecuteQuery(<Query name>
[ , <Connection>] [, <Mode>])

Caution:

The name of the parameter must be the name specified when the query was created in the query editor. This name must differ from an item name.
The indirection operators or the dynamic compilation (Compile) cannot be used with this syntax.
The values of the parameters are reset each time the query is executed with HExecuteQuery.
If at least one parameter is specified in HExecuteQuery, all the parameters specified beforehand are ignored.
The value of the parameter can correspond to NULL.
If one of the parameters is not specified, the corresponding condition is ignored.

All the expected parameters must necessarily be entered.

Using HExecuteQuery to pass parameters to a query

Syntax to use

When a query is run by HExecuteQuery, the last parameters passed to the function correspond to the parameters of the query.

The following syntax is used:

<Result> = HExecuteQuery(<Query Name> [, <Connection>] [, <Mode> [, <Parameters> [, ...]]])

Caution: In this case:

The values of the query parameters must be specified in their initialization order (visible in the query editor). Each parameter must correspond to the expected type. The different parameters are separated by a comma.
To specify the parameters of the query, you must specify the initialization mode of the query.

Avoid specifying all the parameters

Unspecified query parameters

Regardless of the method used to pass parameters to the query, all the query parameters do not necessarily have to be specified. The query conditions that use unspecified parameters will be ignored.

All the expected parameters must necessarily be entered.

Example: Let's consider the "QRY_Customer_LastName_FirstName" query whose SQL code is as follows:

SELECT * FROM CUSTOMER WHERE LASTNAME = {Param1} AND FIRSTNAME = {Param2}

The 2 parameters are specified:
```
HExecuteQuery(QRY_Customers_LastName_FirstName, hQueryDefault, "Smith", "John")
```
will execute the query
SELECT * FROM CUSTOMER WHERE LASTNAME = 'Smith' AND FIRSTNAME = 'John'
Only the last name is specified:
```
HExecuteQuery(QRY_Customers_LastName_FirstName, hQueryDefault, "Smith" )
```
will execute the query
SELECT * FROM CUSTOMER WHERE LASTNAME= 'Smith'

Only the first name is specified. However, the name must be specified and must correspond to Null.

sLastName is Variant = Null 
// Use of variant required 
// for the Null variable (not filled)

HExecuteQuery(QRY_Customers_LastName_FirstName, hQueryDefault, sLastName, "John")

// OR

HExecuteQuery(QRY_Customers_LastName_FirstName, hQueryDefault, Null, "John")

will run the query:

SELECT * FROM CUSTOMER WHERE FIRSTNAME= 'John'