Procedure with select statement as a return

[gwood]

Hi,
Im new to coldfusion and thought i would try webify some of my Sybase 11.5(HPunix) systems to a webpage instead of unix sending a mail. What my problem is that trying to learn how CF returns resultsets from my procedures. I have alot of procedures just returning text (select statements).

begin
select "MIPS NOT COMPLETE"
return
end

***see below for whole proc**
and the problem is that i cannot get CF4.5(win2k) to show the resultset.
I'm just about to go crazy so if anyone could help me or point me to a good CF and storedprocedures pages(other than the help files i would be most appreciated as i would like to automate many things to webpages.

Greg

**************Procedure ****************

create proc pm_chk_mips @rel_date datetime,
@prcs_code char(20),
@serv char(9)
as

create table #pre_req (
pre_req_prcs_code char(20),
stat_code char(1) null
)

declare @pre_req_clss_code char(6),
@stat_flag char(10),
@stat_code_comp char(1)

select @pre_req_clss_code = "mips",
@stat_flag = "srvprocess",
@stat_code_comp = "C" /* Status is "C" - Complete */

insert #pre_req
(pre_req_prcs_code)
select pre_req_prcs_code
from pm_view..pm_pre_req
where prcs_code = @prcs_code
and pre_req_clss_code = @pre_req_clss_code
and (susp_date = null
or susp_date >= convert(smalldatetime, @rel_date)) /* CR 7702 */

update #pre_req
set stat_code = mf.status
from #pre_req p,
idb..mips_flags mf
where mf.val_date = convert(smalldatetime, @rel_date) /* CR 7702 */
and mf.server_name = @serv
and mf.mips_table = p.pre_req_prcs_code
and mf.status_flag = @stat_flag

if exists (select *
from #pre_req
where (stat_code != @stat_code_comp
or stat_code is null))
begin
select "MIPS NOT COMPLETE"
return
end

return

**************COLDFUSION PAGE****************

<CFSTOREDPROC PROCEDURE=&quot;pm_chk_mips&quot;
DATASOURCE=&quot;INV_PROD3_CF&quot;
DEBUG=&quot;No&quot;
RETURNCODE=&quot;No&quot;>

<CFPROCPARAM TYPE=&quot;IN&quot;
CFSQLTYPE=CF_SQL_DATE
VALUE=&quot;4 Jan 01&quot;
DBVARNAME=@rel_date>

<CFPROCPARAM TYPE=&quot;IN&quot;
CFSQLTYPE=CF_SQL_VARCHAR
VALUE=&quot;pm_sp_mips_strt&quot;
DBVARNAME=@prcs_code>

<CFPROCPARAM TYPE=&quot;IN&quot;
CFSQLTYPE=CF_SQL_VARCHAR
VALUE=&quot;INV_PROD3&quot;
DBVARNAME=@serv>

<CFPROCPARAM TYPE=&quot;OUT&quot; CFSQLTYPE=CF_SQL_VARCHAR
VARIABLE=procreturn DBVARNAME=@param1 >

<CFPROCRESULT NAME=RS1>
</CFSTOREDPROC>

<CFOUTPUT QUERY=RS1>
The output param value: '#procreturn#'<br>
#RS1.RecordCount# Columns: #RS1.ColumnList#

<br>
</CFOUTPUT>

 

[swilliams]

If your procedure returns a result set to CF, then use something like this:

<table border=&quot;1&quot;>
<tr>
<td>First Name</td>
</tr>
<tr>
<td>Last Name</td>
</tr>
<tr>
<td>Date Of Birth</td>
</tr>
<cfoutput query=&quot;CFProcresult_Name&quot;>
<tr>
<td>#FirstName#</td>
</tr>
<tr>
<td>#LastName#</td>
</tr>
<tr>
<td>#DOB#</td>
</tr>
</cfoutput>
</table>

The above assumes that the resultset has three fields (FirstName, LastName, DOB) and that the resultset name is &quot;CFProcresult_Name&quot;
Simon

 

[gwood]

all that is coming back is

select &quot;MIPS NOT COMPLETE&quot;

i want to know how to get this to display on a webpage


Greg

 

[swilliams]

I don't know about how sybase works, but in SQL Server 7, you would have to include the @param1 that the line

<CFPROCPARAM TYPE=&quot;OUT&quot; CFSQLTYPE=CF_SQL_VARCHAR
VARIABLE=procreturn DBVARNAME=@param1 >

refers to in your procedure, like this:

create procedure pm_chk
@param1 varchar(100) OUTPUT
as
Select @param1 = 'SELECT &quot;MIPS NOT COMPLETE&quot;'

And then

<CFOUTPUT QUERY=RS1>
The output param value: '#procreturn#'<br>
#RS1.RecordCount# Columns: #RS1.ColumnList#
</CFOUTPUT>
<br>

should display the string.
Simon