CODEWITHSUNDEEP
coldfusioncfquery
WeJava
WeJava

Reputation: 31

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

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

Upvotes: 0

Views: 211

Answers (1)

Adrian J. Moreno
Adrian J. Moreno

Reputation: 14859

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.

Upvotes: 7

Related Questions