Parameters and macros are substitution variables in queries. They improve flexibility of query execution by allowing dynamically change the query (without changing of SQL text) in runtime before the execution. These variables get their values directly before query execution either by user input, command line parameters, report (when the query is executed as part of report), or they can be calculated automatically if they are predefined macros or parameters (see below). Macros are always text variables; their values just replaces macros text in the SQL text before query execution. Unlike macros, parameters have type; their values and types are passed to server and not inserted in SQL text. Macros and parameters inside comments or string literals are ignored by the application SQL engine, but is not recommended to use parameters inside comments, especially in ADO connections. Although macros are often more convenient to use, using parameters are preferable as they help in performance optimization and against SQL injection.

If parameter or macro is not predefined one and its value was not defined in command line or report (Database Tour Pro only), the user will be prompted to enter it in a separate window.

Macros begin with << and end with >>. Macros are defined mainly for substitution when executing queries via command line. In the following SQL example, there is a macro CUST_TYPE_LIST:

SELECT *
FROM payments
WHERE customertype in (<<CUST_TYPE_LIST>>)

The value for this macro can be, for example, the following string:

3, 8, 12, 5

Note that such thing cannot be produced using parameters.

Parameters begin with colon. Parameters, which contain spaces, must be enclosed in single quotes. Parameterized queries are convenient for using the same SQL statement for many data values. In the following SQL example, there is a parameter DATE:

SELECT *
FROM payments
WHERE paydate = :DATE

When the application executes such a query, it suggests user to enter parameter value and data type, and then continues execution. To select parameter data type automatically by the application, you may specify this data type directly in SQL statement in the separate comments right after parameter, as shown in the following example:

SELECT *
FROM orders
WHERE orderno < :ORDERNO /* ParamType=Integer */

There are several predefined parameters, which are calculated automatically by the application and does not require user or command line input:

<<SYSTEM_DATE>>Replaced by current date (with Date type)
<<SYSTEM_DATETIME>>Replaced by current date and time (with DateTime type)
<<SYSTEM_TIME>>Replaced by current time (with DateTime type)
<<SYSTEM_YEAR>>Replaced by current year (with Integer type)
<<SYSTEM_MONTH>>Replaced by current month (with Integer type)
<<SYSTEM_DAY>>Replaced by current day (with Integer Type)

If the predefined parameters begin not with colon, they are interpreted as predefined macros. In case of integer data, the result will be the same, but in case of their data types there may be problems, therefore please don't confuse parameters and macros.

See also