This is my first time working with a stored procedure and I'm not sure that
I'm doing it right.
I can get the procedure to run but I can't get the values to display. What
am I missing
<!-- cfcode -->
<CFSTOREDPROC PROCEDURE="p_pricing_pipeline"
DATASOURCE="#application.datasource#">
<CFPROCPARAM TYPE="IN"
VARIABLE="broker_id"
DBVARNAME="@broker_id"
VALUE="112"
CFSQLTYPE="CF_SQL_INTEGER">
<CFPROCRESULT NAME="test">
</cfstoredproc>
<cfoutput>
!#prequal_num#! - !#borrower_lastname#! - !#highest_status#! - !
#comment_exists#!<br>
</cfoutput>
<!--Stored Procedure-->
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
----------------------------------------------------------------------------------
-- Given a broker, find data for its most recent 5 prequals.
--
-- Date Author Change
-- 07/07/00 D Noble Created.
----------------------------------------------------------------------------------
create procedure dbo.p_Pricing_Pipeline (
@broker_id int
)
as
set nocount on
create table #prequal (
prequal_num int not null
,borrower_lastname varchar(96) null
)
insert #prequal
select top 5
prequal_num
,borrower_lastname
from prequal
where broker_id = @broker_id
order by prequal_num desc
create table #half_results (
prequal_num int not null
,borrower_lastname varchar(96) null
,loan_status_rank tinyint null
,number_of_comments int null
)
insert #half_results
select
p.prequal_num
,p.borrower_lastname
,min(ls.loan_status_rank)
--,sum(isnull(c.prequal_num,0))
,sum(
case
when c.prequal_num is null then 0
else 1 end
)
from
#prequal p
join scenario s on p.prequal_num = s.prequal_num
join loanstatus ls on s.loan_status_id = ls.loan_status_id
left join comment c on p.prequal_num = c.prequal_num
group by
p.prequal_num
,p.borrower_lastname
select
prequal_num as prequal_num
,borrower_lastname as borrower_lastname
,loan_status_rank as highest_status
,case number_of_comments
when 0 then 0
else 1 end as comment_exists
from #half_results
drop table #prequal
drop table #half_results
return 0
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
TIA,
--K
====================
Katrina Chapman
Consultant
Ameriquest Mortgage
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
