The purpose of parameterized queries.

By: Ben Matterson

Abstract: A breif description of the advantage of prameterized queries over regular queries.

What is a parameterized query for?

The main advantage of a parameterized query is that the query does not need to be prepared each time it is run. A good example of this is scrolling a master record while the detail is refreshed based on the new record. In this case you are essentaily running the same query repeatedly, with different values in the WHERE clause. If you do this with a regular query, you will have to prepare the query each time. If you use a parameterized query, you prepare the query once. Then you can change the parameters, and execute the statement as many times as you wish without having to prepare the query again.

With most Delphi query components, changing the SQL requires re-parsing it, and re-allocating space for the parameters. When you use a parameterized query, changing the parameters does not mean changing the SQL, and thus the SQL is not re-parsed, saving time.

A parameterized query can be twice as fast in a situation like this.

Bill Todd of Team B and The Database Group wrote about parameterized queries and master detail relationships (scroll down) in his paper, Introduction to InterBaseExpress, given at the 11th annual BorCon.

Another use of parameterized queries is for inserting data into blob fields.

Server Response from: ETNASC03