CODEWITHSUNDEEP
loopsselectstored-procedurescoldfusion
F Z
F Z

Reputation: 21

How to select from a Query table returned by a stored procedure in ColdFusion?

I have a stored procedure which returns a bunch of queries and I need to select a few queries based on the information in two or more columns. I have used cfloop query ="resultName" before, but this time I cannot simply loop through it. Is this even possible?

Code: 

<!--- Run procedure to generate result set/query --->
<cfstoredproc datasource="#XXX#" procedure="XXX">
    <cfprocparam type="In" value="12" cfsqltype="CF_SQL_INTEGER">
    <cfprocparam type="In" value="3" cfsqltype="CF_SQL_INTEGER">
    <cfprocparam type="In" value="1" cfsqltype="CF_SQL_BIT">

    <cfprocresult name="QueryU">
</cfstoredproc>

QueryU consists of a bunch of queries from which I want to select one column value based on two other column values.

Update:

The question is really, "how do I select a subset of records from an existing query?"

Upvotes: 0

Views: 1504

Answers (2)

F Z
F Z

Reputation: 21

The solution was to use a Query of Queries.

Here is an example based on the documentation: 

<!--- Run procedure to generate result set/query --->
<cfstoredproc datasource="#XXX#" procedure="XXX">
    <cfprocparam type="In" value="12" cfsqltype="CF_SQL_INTEGER">
    <cfprocparam type="In" value="3" cfsqltype="CF_SQL_INTEGER">
    <cfprocparam type="In" value="1" cfsqltype="CF_SQL_BIT">

    <cfprocresult name="QueryU">
</cfstoredproc>

<!--- Run QoQ to get a subset of the results (dbtype=query, no data source) --->
<cfquery dbtype="query" name="detail"> 
    SELECT Emp_ID, FirstName, LastName
    FROM   QueryU
    WHERE  LastName =<cfqueryparam value="#LastNameSearch#" 
                 cfsqltype="cf_sql_char" maxLength="20">
</cfquery>

Upvotes: 0

steve
steve

Reputation: 1490

Based on the Adobe documentation, you need to list a cfprocresult for each result set you are sending back.

<cfprocresult name="query1">
<cfprocresult name="query2">
<cfprocresult name="query3">

You can then reference the fields that you need while outputting each query.

Upvotes: 2

Related Questions