Using Parameters in Queries

A parameterized SQL statement contains parameters, or variables, the values of which can be varied at design time or runtime. Parameters can replace data values, such as those used in a WHERE clause for comparisons, that appear in an SQL statement. Ordinarily, parameters stand in for data values passed to the statement. For example, in the following INSERT statement, values to insert are passed as parameters:

Copy Code

INSERT INTO Country (Name, Capital, Population)
VALUES (:Name, :Capital, :Population)

In this SQL statement, :Name, :Capital, and :Population are placeholders for actual values supplied to the statement at runtime by your application. Note that the names of parameters begin with a colon. The colon is required so that the parameter names can be distinguished from literal values. You can also include unnamed parameters by adding a question mark (?) to your query. Unnamed parameters are identified by position, because they do not have unique names.

Before the dataset can execute the query, you must supply values for any parameters in the query text. TQuery, TIBQuery, TSQLQuery, and client datasets use the Params property to store these values. TADOQuery uses the Parameters property instead. Params (or Parameters) is a collection of parameter objects (TParam or TParameter), where each object represents a single parameter. When you specify the text for the query, the dataset generates this set of parameter objects, and (depending on the dataset type) initializes any of their properties that it can deduce from the query.

Note: You can suppress the automatic generation of parameter objects in response to changing the query text by setting the ParamCheck property to False. This is useful for data definition language (DDL) statements that contain parameters as part of the DDL statement that are not parameters for the query itself. For example, the DDL statement to create a stored procedure may define parameters that are part of the stored procedure. By setting ParamCheck to False, you prevent these parameters from being mistaken for parameters of the query.

Parameter values must be bound into the SQL statement before it is executed for the first time. Query components do this automatically for you even if you do not explicitly call the Prepare method before executing a query.

Tip: It is a good programming practice to provide variable names for parameters that correspond to the actual name of the column with which it is associated. For example, if a column name is "Number," then its corresponding parameter would be ":Number". Using matching names is especially important if the dataset uses a data source to obtain parameter values from another dataset. This process is described in Establishing master/detail relationships using parameters.

The following topics describe how to specify the datatypes and values of parameters for your query: