Parameters allow the same SQL statement to be used with different data values, and are placeholders for those data values. At runtime, the application prepares the query with the parameters and fills the parameter with a value before the query is executed. When the query is executed, the data values assigned to the parameters are substituted for the parameter placeholder and the SQL statement is executed.

Specifying Parameters in SQL

---

Parameter markers can be used in SQL SELECT, INSERT, UPDATE, and DELETE statements in place of constants. Parameters are identified by a preceding colon (:). For example:

SELECT Last_Name, First_Name
FROM Customer
WHERE (Last_Name=:LName) AND (First_Name=:FName)

Parameters are used to pass data values to be used in WHERE clause comparisons and as update values in updating SQL statements such as UPDATE or INSERT. Parameters cannot be used to pass values for Identifiers. The following example uses the TotalParam parameter to pass the data value that needs to be assigned to the ItemsTotal column for the row with the OrderNo column equal to 1014:

UPDATE Orders
SET ItemsTotal = :TotalParam
WHERE (OrderNo = 1014)

Populating Parameters with the TEDBQuery Component

---

You can use the TEDBQuery Params property to populate the parameters in an SQL statement with data values. You may use two different methods of populating parameters using the Params property:

   • By referencing each parameter by its index position in the available list of parameters
   • By reference each parameter by name using the ParamByName method

The following is an example of using the index positions of the parameters to populate the data values for an INSERT SQL statement:

begin
   with MyQuery do
      begin
      SQL.Clear;
      SQL.Add('INSERT INTO Country (Name, Capital, Population)');
      SQL.Add('VALUES (:Name, :Capital, :Population)');
      Params[0].AsString := 'Lichtenstein';
      Params[1].AsString := 'Vaduz';
      Params[2].AsInteger := 420000;
      ExecSQL;
      end;
end;

The next block of code is an example of using the TEDBQuery ParamByName method in order to populate the data values for a SELECT SQL statement:

begin
   with MyQuery do
      begin
      SQL.Clear;
      SQL.Add('SELECT *');
      SQL.Add('FROM Orders');
      SQL.Add('WHERE CustID = :CustID');
      ParamByName('CustID').AsFloat:=1221;
      Open;
      end;
end;

Preparing Parameterized Queries

---

It is usually recommended that you manually prepare parameterized queries that you intend to execute many times with different parameter values. This can result in significant performance improvements since the process of preparing a query can be time-consuming. The following is an example of inserting 3 rows with different values using a manually-prepared, parameterized query:

begin
   with MyQuery do
      begin
      SQL.Clear;
      SQL.Add('INSERT INTO Customer (CustNo, Company');
      SQL.Add('VALUES (:CustNo, :Company)');
      { Manually prepare the query }
      Prepare;
      ParamByName('CustNo').AsInteger:=1000;
      ParamByName('Company').AsString:='Chocolates, Inc.';
      ExecSQL;
      ParamByName('CustNo').AsInteger:=2000;
      ParamByName('Company').AsString:='Flowers, Inc.';
      ExecSQL;
      ParamByName('CustNo').AsInteger:=3000;
      ParamByName('Company').AsString:='Candies, Inc.';
      ExecSQL;
      end;
end;