Using Prepared Statements in Queries

When defining a custom query for an event (using the Default Values, Valid Values, or Event Validation forms), you can use prepared statements in the query. A prepared statement can help improve the application's performance by executing the query more efficiently.

The query format for a prepared statement is different than the format of a query without a prepared statement.

For example, in this event when a person selects the Project Number prompt, the Project Description prompt displays automatically in a read-only field.

The Project Description prompt uses a Default Value with the following query:

select description from charge_element where element_value = '[%f_projnum%]'

The query above selects the Project Description based on the value in the Project Number prompt (f_projnum).

This query can also be written with a prepared statement:

select description from charge_element where element_value = [%f_projnum:nvarchar%]

Notice the format of the prepared statement has the semantic or prompt followed by a colon (:) and the data type. It also does not have single quotes.

Allowed Data Types for Prepared Statements

You can use the data types shown below in your prepared statements.

The first column is the data type to use in the prepared statement. The next two columns show the name of this data type in an Oracle or SQL Server database.