Passes queries or SQL statements to a data source.
It is recommended that you use the cfqueryparam tag within
every cfquery tag, to help secure your databases from
unauthorized users


 queryExecute(sql, params, options);

Attribute Reference for the cfquery tag


Required: No
Name of query. Used in page to reference query record set.
Must begin with a letter. Can include letters, numbers,
and underscores.


Required: No
Name of data source from which query gets data. As of CF 9+ you can specify a default datasource in Application.cfc using the variable this.datasource


Required: No
query. Use this value to specify the results of a query as


Required: No
Overrides username in data source setup.


Required: No
Overrides password in data source setup.


Required: No
Default: -1
Maximum number of rows to return in record set.
-1 returns all records.


Required: No
Maximum rows to get at a time from server. Range: 1 - 100.
Might not be supported by some database systems.


Required: No
Maximum number of seconds that each action of a query is
permitted to execute before returning an error. The
cumulative time may exceed this value.

For JDBC statements, CFML sets this attribute. For
other drivers, check driver documentation.


Required: No
Date value (for example, April 16, 1999, 4-16-99). If date
of original query is after this date, CFML uses
cached query data. To use cached data, current query must
use same SQL statement, data source, query name, user name,

A date/time object is in the range 100 AD-9999 AD.

When specifying a date value as a string, you must enclose
it in quotation marks.


Required: No
Timespan, using the CreateTimeSpan function. If original
query date falls within the time span, cached query data is
used. CreateTimeSpan defines a period from the present,
back. Takes effect only if query caching is enabled in the

To use cached data, the current query must use the same SQL
statement, data source, query name, user name, and password.


Required: No
Default: NO
Yes: If debugging is enabled, but the Administrator
Database Activity option is not enabled, displays SQL
submitted to datasource and number of records returned
by query.
No: If the Administrator Database Activity option is
enabled, suppresses display.


Required: No
CF 8+ Specifies a name for the structure in which cfquery returns
the result variables.
* SQL: The SQL statement that was executed. (string)
* Cached: If the query was cached. (boolean)
* SqlParameters: An ordered Array of cfqueryparam values. (array)
* RecordCount: Total number of records in the query. (numeric)
* ColumnList: Column list, comma seperated. (numeric)
* ExecutionTime: Execution time for the SQL request. (numeric)
* GENERATEDKEY: CF 9+ If the query was an INSERT with an identity or autoincrement value the value of that ID is placed in this variable.


Required: No
CF 9+ A structure of ORM Options when used for HQL queries (9.0.1+).


Required: No
CF 10+ A value to serve as cache identifier when cachedWithin or cachedAfter are specified.


Required: No
CF 10+ The name of the region cachedWithin or cachedAfter are specified.


Required: No
CF 10+ A structure containing properties to be set on the database connection.


Required: No
Default: false
CF 10+ When true returns a struct with the clientInfo argument value passed by the last query

Links more information about cfquery

Examples sample code using the cfquery tag

Example CFQuery with CFQueryParam

Shows how to use a cfqueryparam tag within cfquery.

<cfquery name="news">
    SELECT id,title,story
    FROM news
    WHERE id = <cfqueryparam value="" cfsqltype="cf_sql_integer">

Script Syntax using QueryExecute

Also see the Tags Implemented as Components section for another method of using in script.

myQuery = queryExecute(
 "SELECT myCol1, myCol2 FROM myTable 
  WHERE myCol1 = :myid 
  ORDER BY myCol1 ASC ", 
  {myid: 5}, 
  {datasource = "myDSN"} 

Simple Query of Query Example

A dummy query is first created from scratch using queryNew, then sorted. A query of query is performed by specifying dbtype="query" and then using a query object variable name as in the FROM statement.

<!--- create a dummy query using queryNew --->
<cfset news = queryNew("id,title", "integer,varchar")>
<cfset queryAddRow(news)>
<cfset querySetCell(news, "id", "1")>
<cfset querySetCell(news, "title", "Dewey defeats Truman")>
<cfset queryAddRow(news)>
<cfset querySetCell(news, "id", "2")>
<cfset querySetCell(news, "title", "Men walk on Moon")>
<cfset writeDump(news)>

<!--- run QofQ (query of query) --->
<cfquery name="sortedNews" dbtype="query">
    SELECT id, title FROM news
    ORDER BY title DESC
<cfset writeDump(sortedNews)>

Script Syntax using new Query()

This syntax was implemented by script-based components in CF 9 & 10. It is superceeded by queryExecute() in CF11.

queryObj = new Query(
 sql = "SELECT col1, col2
 FROM myTable
 WHERE id=:id"
queryObj.addParam(name="id",, cfsqltype="cf_sql_integer");

Fork me on GitHub