<cfquery>

<cfquery> is how ColdFusion talks to a database — it runs a SQL statement and gives you back a query object you can loop through, just like the query loop covered in the Loops module. Its CFScript equivalent is the queryExecute() function.

Learning Objectives

After completing this lesson, you'll be able to:

Run a SELECT query with <cfquery> and queryExecute().
Access query results — looping through rows, recordCount, and columnList.
Run INSERT, UPDATE, and DELETE statements.
Use the result attribute to get metadata about a query's execution.

A Basic SELECT Query

Tag Syntax

<cfquery name="employees" datasource="cfdocexamples">
    SELECT Emp_ID, FirstName, LastName
    FROM Employees
</cfquery>

<cfloop query="employees">
    <cfoutput>#FirstName# #LastName#<br></cfoutput>
</cfloop>

CFScript

<cfscript>

employees = queryExecute(
    "SELECT Emp_ID, FirstName, LastName FROM Employees",
    {},
    {datasource: "cfdocexamples"}
);

for (row in employees) {
    writeOutput(row.FirstName & " " & row.LastName & "<br>");
}

</cfscript>

NOTE

queryExecute() takes the SQL string, a struct of parameters (empty {} here — covered properly in the next lesson), and a struct of options like datasource.

Checking How Many Rows Came Back: recordCount

Tag Syntax

<cfquery name="employees" datasource="cfdocexamples">
    SELECT Emp_ID FROM Employees
</cfquery>

<cfoutput>
#employees.recordCount# employees found.
</cfoutput>

NOTE

recordCount is 0, not an error, when a query legitimately finds nothing — always something to check before assuming a result exists.

Getting Column Names: columnList

Tag Syntax

Simulated output — illustrative only, not a live ColdFusion/Lucee server.

INSERT, UPDATE, and DELETE

The same tag runs any SQL statement, not just SELECT — there's just nothing to loop through afterward.

Tag Syntax — INSERT

<cfquery datasource="cfdocexamples">
    INSERT INTO Employees (FirstName, LastName)
    VALUES ('Priya', 'Sharma')
</cfquery>

Tag Syntax — UPDATE

<cfquery datasource="cfdocexamples">
    UPDATE Employees
    SET LastName = 'Verma'
    WHERE Emp_ID = 12
</cfquery>

Tag Syntax — DELETE

<cfquery datasource="cfdocexamples">
    DELETE FROM Employees
    WHERE Emp_ID = 12
</cfquery>

NOTE

An INSERT/UPDATE/DELETE query doesn't need a name attribute, since there's no result set to reference afterward.

The result Attribute

Gives you metadata about the query's execution — useful for INSERT statements especially, to find out the ID that was just generated.

Tag Syntax

<cfquery datasource="cfdocexamples" result="insertResult">
    INSERT INTO Employees (FirstName, LastName)
    VALUES ('Priya', 'Sharma')
</cfquery>

<cfoutput>
Rows affected: #insertResult.recordCount#<br>
Generated ID: #insertResult.generatedKey#
</cfoutput>

NOTE

The result struct includes sql, cached, sqlParameters, recordCount, columnList, executionTime, and generatedKey (for inserts with an auto-increment column).

Real-World Example: A Simple Employee Directory

Tag Syntax

<cfquery name="employees" datasource="cfdocexamples">
    SELECT FirstName, LastName, Email
    FROM Employees
    ORDER BY LastName
</cfquery>

<cfoutput>
<p>#employees.recordCount# employees</p>
</cfoutput>

<cfloop query="employees">
    <cfoutput>#LastName#, #FirstName# — #Email#<br></cfoutput>
</cfloop>

Common Beginner Mistakes

Assuming an empty result is an error

A query that legitimately finds nothing still succeeds — recordCount is just 0. Check recordCount before assuming a row exists, rather than expecting an exception.

Forgetting the name attribute isn't needed for INSERT/UPDATE/DELETE

There's no result set to reference for those statements, so name is optional — though giving one is harmless if you want to.

Building SQL by directly concatenating user input into the query string

This is a SQL injection risk — covered properly in the next lesson on cfqueryparam, which is what actually protects against it.

Best Practices

Always check recordCount before assuming a SELECT returned anything.
Never concatenate raw user input into SQL — use cfqueryparam (next lesson) for any dynamic value.
Use the result attribute on INSERT statements when you need the generated ID back.

Interview Questions

What's the CFScript equivalent of <cfquery>?

queryExecute(sql, params, options).

How do you check how many rows a query returned?

The query's recordCount property.

Does an INSERT query need a name attribute?

No — it's only needed when there's a result set to loop through afterward, like a SELECT.

What does the result attribute give you that recordCount alone doesn't?

Additional metadata like executionTime, the exact SQL that ran, and generatedKey — the auto-generated ID from an INSERT.

Summary

In this lesson, you learned how to run SQL with <cfquery> and its CFScript equivalent queryExecute(), access results via recordCount and columnList, run INSERT/UPDATE/DELETE statements, and use the result attribute for execution metadata.

What's Next?

The next lesson covers cfqueryparam — the essential tool for safely including dynamic values in SQL and preventing SQL injection.