Whatever message this page gives is out now! Go check it out!

QueryExecute

Last update:
May 18, 2026

Description

Apart from the generic script syntax for the tag, there is another script alternative to cfquery tag, provided by the QueryExecute function. This function simplifies the query execution in the CFScript block.
This function lets you pass unnamed parameters to the query. Use '?' (question mark) as the place-holder for the parameters. The values to the parameters should be passed as an array toparams.
Note that the following points are applicable:
  • If there are any Query parameters, they must be passed as the second argument of the function. It can be either a struct or an array.
  • If you need to provide additional attribute as a Query parameter (like CFSQLType, list, and separator), you can pass these as struct against that column name.
  • The Query and datasource properties will be passed as the third argument.
The Query tag is also available as a ColdFusion function apart from the generic script syntax for the CFQuery tag. This function simplifies the query execution in the CFScript block.

Syntax

QueryExecute(sql,params,options,returnType)

Parameters

Name
Required
Description
sql
Yes
SQL string to execute.
params
No
Array or Struct of parameter values.
options
No
Struct containing query options. Values are:
  • datasource
  • cachename
  • dbtype
  • disableAutoGenKeys
For a list of supported queryOptions, see the documentation for <cfquery>. All except the name option are supported.
returnType
No
The return type of the query object. The following are the return types:
  • Array
  • JSON/Array
  • Query (default)

See also

cfquery

History

ColdFusion (2021 release): Added the attribute returnType as an option, with the following return types:
  • Array
  • JSON/Array
  • Query (default)
ColdFusion (2018 release): Added the value disableAutoGenKeys in options.
ColdFusion 11: Added this function
Example 1
<cfset qoptions = {result="myresult", datasource="artGallery", fetchclientinfo="yes"}>

<cfset myquery = QueryExecute("select * from art where ARTID < 5", [] ,qoptions)>
<cfdump var="#myQuery#" >

<cfset myquery1 = QueryExecute("select * from art where ARTID < ?", [4] ,qoptions)>
<cfdump var="#myQuery1#" >

<cfset myquery2 = QueryExecute("select * from art where ARTID < :artid and artistid=:aid ", {artid={value=100}, aid=2} ,qoptions)>
<cfdump var="#myQuery2#" >
Example 2
QueryExecute ("select from Employees where empid=1");

QueryExecute("select from Employee where country=:country and citizenship=:country", {country='USA'});

QueryExecute("select from Employee where country=:country and citizenship=:country", {country={value='USA',
CFSQLType='CF_SQL_CLOB', list=true, other‐queryParam_attributes}, });

QueryExecute("select from Employee where country=:country and citizenship=:country", {country='USA'},
{datasource=”cfartgallery”, cachename=”employees”})

QueryExecute ("select from Artists where artistid=? and country=?", [1, “USA”], {datasource="cfartgallery"});

Example 3

The example searches for all employees whose emp id is between 4-14 and displays the results.
<cfscript>
       myResult=QueryExecute(("SELECT * FROM EMPLOYEES WHERE EMP_ID BETWEEN :low AND :high"),{low=4,high=14},
       {datasource="cfdocexamples"});
       WriteDump(myResult);
</cfscript>
Output
Figure: All employees whose emp id is between 4-14
All employees whose emp id is between 4-14
Example 4
This example searches for records in the table for a particular id and name.
<cfscript>
       myResult=QueryExecute("SELECT * FROM MYEMPLOYEES WHERE ID=:ID AND Name=:NAME",{ID="004",Name="Jill"},
       {datasource="cfdocexamples"});
       WriteDump(myResult);
</cfscript>
Output
Figure: Record for a particular id and name
Record for a particular id and name
Example 5
This example searches for all employees working in sales and stays in either Newton or San Francisco.
<cfscript>
       myResult=QueryExecute(("SELECT * FROM EMPLOYEES WHERE DEPARTMENT='Sales' AND LOCATION in (?,?)"),["Newton","San Francisco"],
       {datasource="cfdocexamples"});
       WriteDump(myResult);
</cfscript>
Output
Figure: Employees working in sales and stays in either Newton or San Francisco
Employees working in sales and stays in either Newton or San Francisco

Example 6

The sample below used the parameter disableAutoGenKeys.
<cfscript>
     q=QueryExecute("Select * from art where artid=1",{}{datasource="cfartgallery", disableAutoGenKeys="false"}
);
     writedump(q);
</cfscript>

Example 7

<cfscript>
    cfhttp(name="myQuery", 
           url="https://raw.githubusercontent.com/sauravg94/test-repo/master/MOCK_DATA.csv", 
           firstrowasheaders="true",
           method="GET");
    myResult=QueryExecute(("SELECT * from myQuery WHERE id BETWEEN :low AND :high"),{low=100,high=500},{dbtype="query"})
    writeDump(myResult)
</cfscript>

Example 8

<cfscript> 
  // QueryExecute return type array 
  qoptions = {result="myresult", datasource="cfartgallery",returntype="array"} 
  myquery = QueryExecute("select ARTID,ARTISTID,ARTNAME from art where ARTID < 2", [] ,qoptions); 
  writeOutput(myquery[1].ARTID &" ") 
  writeOutput(myquery[1].ARTISTID &" ") 
  writeOutput(myquery[1].ARTNAME &" ") 
</cfscript>

Example 9

<cfscript> 
  // QueryExecute return type "json/array" 
  qoptions = {result="myresult", datasource="cfartgallery",returntype="json/array"} 
  myquery = QueryExecute("select ARTID,ARTISTID,ARTNAME from art where ARTID < 2", [] ,qoptions); 
  record=deserializeJSON(myquery) 
  writeOutput(record[1].ARTID &" ") 
  writeOutput(record[1].ARTISTID &" ") 
  writeOutput(record[1].ARTNAME &" ") 
  writeOutput(myquery) 
</cfscript>

Example 10

<cfscript> 
  // QueryExecute return type default query 
  qoptions = {result="myresult", datasource="cfartgallery"} 
  myquery = QueryExecute("select ARTID,ARTISTID,ARTNAME from art where ARTID < 2", [] ,qoptions); 
  record=QueryGetRow(myQuery,1) 
  writeOutput(record.ARTID &" ") 
  writeOutput(record.ARTISTID &" ") 
  writeOutput(record.ARTNAME &" ") 
</cfscript>

Share this page

Was this page helpful?
We're glad. Tell us how this page helped.
We're sorry. Can you tell us what didn't work for you?
Thank you for your feedback. Your response will help improve this page.

On this page