dynamically running stored procedures

[kramers]

I have a query getting a list of procedures and arguments from a table. Using the results I'm trying to run a series of stored procedures for example this comes back from the query:

sp='sp_gpa' argument='2.5',
sp='sp_sex' argument='m'

and in the page I have the following code:

<cfoutput>
<cfset i=0>
<cfloop query="queryname">
<cfset i=i+1>
<cfstoredproc datasource="gooddatasource" procedure="#sp#">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" value="#argument#">
<cfprocresult resultset="1" name="proc#i#">
</cfstoredproc>
<cfset showresultset="proc"&#i#&".recordcount">
#evaluate(showresultset)#
</cfloop>
</cfoutput>

Admittedly this is a bit convoluted, but from the debug pages it looks like the stored procedures are running, but the recordsets always come back with zero results, but when I run the stored procedures manually I get a resultset.

any ideas?

 

[Bammy217]

Hi,

Try changing:

<cfset showresultset="proc"&#i#&".recordcount">
#evaluate(showresultset)#

to:

#Evaluate("proc" & i & ".RecordCount")#

We never fail, we just find that the path to succes is never quite what we thought...

 

[kramers]

That didn't work. It's not so much that Coldfusion is having difficulty dereferencing the resultset. It's that the recordcount is always zero.

 

[ecobb]

Just out of curiousity, try this:

<cfoutput query="queryname">
    <cfstoredproc datasource="gooddatasource" procedure="#sp#">
      <cfprocparam type="in" cfsqltype="cf_sql_varchar" value="#argument#">
      <cfprocresult resultset="1" name="#sp#">
    </cfstoredproc>
#sp.RecordCount#
</cfoutput>

Does this give you what you want?



Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher

 

[kramers]

recordcount is undefined in sp.

New thought: I hardcoded the stored procedure to run in cfquery tags like so.

<cfquery datasource="soe" name="query">
execute sp_name 'argument'
</cfquery>

it tells me it executes with no results, but when I copy the text and run it in native sql it works. What's the deal. I have rights to run stored procedures, it's just that for some reason this one doesn't give me any results. What's the deal. I'm going to go crazy. I'm not even doing this dynamically now.

 

[ecobb]

Try just running the Stored Procedure with the info hard coded in it using the cfstoredproc tag. What does that do?



Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher