Can I return columns in text form from a function with sql query in coldfusion

Question

I want to write a function in CF, that has a query that returns multiple columns. I researched and read and could only find examples of functions with sql queries only returning the table view of the struct.

How would I write a function in CF with sql queries and output each column in text format

I been googling it and looking on tutorial websites, with no luck

<cffunction name="queryListEmployee" returntype="query" output="false">
<cfargument name="EMPID" type="numeric" default="1"/>
<cfset var listEmployee = ""/>
    <cfquery name="local.listContractors" datasource="DB">
        SELECT E.FIRSTNAME, E.LASTNAME
        FROM EMPLOYEE E
        WHERE E.ID = <cfqueryparam value="#arguments.EMPID#" cfsqltype="cf_sql_decimal" scale="0"/>
    </cfquery>
<cfreturn local.listEmployee/>

I want to be able to output column names like queryListEmployee.firstname and queryListEmployee.lastname

If the function returns a query object, the calling code can output any (or all) of the columns as it sees fit. What do you mean by "output each column in text format"? Please provide an example of the desired result. — SOS, May 29 '19 at 17:06
Probably a typo, but the name of your query might not be what you had in mind. — Dan Bracuk, May 29 '19 at 18:07
OT: Is your ID field really a decimal and not an integer? See: https://stackoverflow.com/questions/19521826/nuances-between-cf-sql-decimal-and-cf-sql-numeric — James A Mohler, May 29 '19 at 18:24
Are you looking for the function to generate output, or return results? Can't you just return the results, set a variable, and iterate over that? — James A Mohler, May 29 '19 at 18:26

score 7 · Answer 1 · answered May 29 '19 at 19:11

You've got the idea right. Just a few tweaks:

<cffunction name="getEmployeeByID" returntype="query" output="false">
    <cfargument name="EMPID" type="numeric" default="1"/>
    <cfquery name="local.employee" datasource="DB">
        SELECT E.FIRSTNAME, E.LASTNAME
        FROM EMPLOYEE E
        WHERE E.ID = <cfqueryparam 
                          value="#arguments.EMPID#" 
                          cfsqltype="cf_sql_integer"/>
    </cfquery>
    <cfreturn local.employee/>
</cffunction>

Then call this function like so:

<cfset qEmployee = getEmployeeByID(1)>

and output the data like this:

<cfoutput query="qEmployee">
    <li>#qEmployee.FIRSTNAME# #qEmployee.LASTNAME#</li>
</cfoutput>

This function will only ever return one record. You can search and figure out how to dynamically adjust the search criteria in order to return multiple records.

You might check out http://www.learncfinaweek.com/ to get learn more of the basics of ColdFusion.

Can I return columns in text form from a function with sql query in coldfusion

1 Answers1