stored proc return param used in select statement 1

[gwood]

Hello,
I have a stored procedure and a simple select statement. What im trying to do is display all results in a table but doing a lookup from a return value in the procedure as the select statement in the other query. What is happening though is that you cannot have nested CFOUTPUT. any ideas?? loops etc.. does a stored proc resultset let you loop through?

Greg

<CFSTOREDPROC PROCEDURE=&quot;sp_who&quot;
DATASOURCE=&quot;INV_PROD3_CF&quot;
DEBUG=&quot;Yes&quot;
RETURNCODE=&quot;Yes&quot;>
<CFPROCRESULT NAME=RS1>
</CFSTOREDPROC>

<CFQUERY NAME=&quot;infraLookup&quot;
DATASOURCE=&quot;INFRA_CF&quot;
DBNAME=&quot;infra5&quot;>
Select SURNAME,FIRST_NAME,ATTRIBUTE_1 from infra5..AR_USER_ATTRIBUTES
where ATTRIBUTE_1 = &quot;#UCASE(RS1.loginame)#&quot;
</CFQUERY>

<CFOUTPUT QUERY=RS1>

<td>#RS1.spid#</td>
<td>#RS1.status#</td>
<td>#RS1.loginame#</td>
<td>#RS1.blk#</td>
<CFOUTPUT QUERY=infralookup>#SURNAME#</CFOUPUT>
<td>#RS1.dbname#</td>
<td>#RS1.hostname#</td>
<td>#RS1.cmd#</td>
</tr>

</CFOUTPUT>

 

[swilliams]

I think there is a much simpler way to do this.

Can you not do just one select statement to return all of the information you need, using JOINS between the tables in question:

In the SQL statement below I am assuimg the following (as you did not include the select statement in your stored procedure on your post):
1. you are selecting fields spid, status, loginname, blk, dbname, hostname, cmd from a table - I will assume that this table is called tbl_who - in the stored procedure.
2. the field loginname in the table tbl_who holds the same information as the field ATTRIBUTES_1 in the table infra5..AR_USER_ATTRIBUTES

Then you can use the SQL statement:

SELECT tbl_who.spid, tbl_who.status, tbl_who.loginname, tbl_who.blk, tbl_who.dbname, tbl_who.hostname, tbl_who.cmd, infra5..AR_USER_ATTRIBUTES.SURNAME, infra5..AR_USER_ATTRIBUTES.FIRST_NAME FROM tbl_who INNER JOIN infra5..AR_USER_ATTRIBUTES ON tbl_who.loginname = infra5..AR_USER_ATTRIBUTES.ATTRIBUTES_1

(PS - the table name infra5..AR_USER_ATTRIBUTES looks a bit iffy to me).

Failing that, you can use the following (NOTE that this has not been tested and so you may have to change the attributes on the CF tags slightly):

<CFSTOREDPROC PROCEDURE=&quot;sp_who&quot;
DATASOURCE=&quot;INV_PROD3_CF&quot;
DEBUG=&quot;Yes&quot;
RETURNCODE=&quot;Yes&quot;>
<CFPROCRESULT NAME=RS1>
</CFSTOREDPROC>

<table>
<CFLOOP QUERY=RS1>
<tr>
<td>#RS1.spid#</td>
<td>#RS1.status#</td>
<td>#RS1.loginame#</td>
<td>#RS1.blk#</td>
<CFQUERY NAME=&quot;infraLookup&quot; DATASOURCE=&quot;INFRA_CF&quot; DBNAME=&quot;infra5&quot;>
Select SURNAME,FIRST_NAME,ATTRIBUTE_1 from
infra5..AR_USER_ATTRIBUTES
where ATTRIBUTE_1 = &quot;#UCASE(RS1.loginame)#&quot;
</CFQUERY>
<td><CFOUTPUT QUERY=infralookup>#SURNAME#</CFOUPUT></td>
<td>#RS1.dbname#</td>
<td>#RS1.hostname#</td>
<td>#RS1.cmd#</td>
</tr>
</CFLOOP>
</table>
Simon

 

[gwood]

Thanks swilliams but i went away learnt to loop a stored proc result set!! it now works a dream and even send;s me a message when there is blocking. I have set it up to run every minute on the server
Greg

<CFOUTPUT QUERY=&quot;RS1&quot; GROUP=spid>
<td>#RS1.spid#</td>
<td>#RS1.status#</td>
<td>#RS1.loginame#</td>
<CFIF #RS1.loginame# is NOT &quot;&quot;>
<CFLOOP INDEX=&quot;greg&quot; FROM=&quot;1&quot; TO=&quot;1&quot;>
<CFQUERY NAME=&quot;infraLookup&quot;
DATASOURCE=&quot;INFRA_CF&quot;
DBNAME=&quot;infra5&quot;>
Select SURNAME,FIRST_NAME,ATTRIBUTE_1,TEL
from infra5..AR_USER_ATTRIBUTES
where ATTRIBUTE_1 = &quot;#UCASE(RS1.loginame)#&quot;
</CFQUERY>

<CFOUTPUT>
<td>#infraLookup.FIRST_NAME# #infraLookup.SURNAME# </td>
<td>#infraLookup.TEL#</td>
</CFOUTPUT>
</CFLOOP>
<CFELSE>
<td></td>
<td></td>
</CFIF>
<CFIF #RS1.blk# GT 0>
<CFFILE ACTION=&quot;Append&quot;
FILE=&quot;K:\AppSup~1\Message\other.log&quot;
OUTPUT=&quot;#LSDateFormat(Now(), &quot;d/m/&quot# #TimeFormat(todayDate, 'HH:mm')# -- #RS1.loginame# blocking on INV_PROD3&quot;>
<CFEXECUTE NAME=&quot;C:\winnt\system32\net.exe&quot;
ARGUMENTS=&quot; send wodggq,nyntnq,bckboq #RS1.loginame#blocking_INV_PROD3&quot;>
</CFEXECUTE>
</CFIF>

<td>#RS1.blk#</td>
<td>#RS1.dbname#</td>
<td>#RS1.hostname#</td>
<td>#RS1.cmd#</td>
</tr>
</CFOUTPUT>

 

[swilliams]

I still think this can and SHOULD be done by the way I suggested first. This way involve less database reads and therefore will be quicker.
Simon