Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank
you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!
Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank
you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!
Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank
you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!
Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank
you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!
Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank
you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!
Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank
you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!
I don't think I said that enough.
--K
====================
Katrina Chapman
Consultant
Ameriquest Mortgage
[EMAIL PROTECTED]
tate.ky.us To: [EMAIL PROTECTED]
cc:
07/10/00 12:03 Subject: RE: Stored Procedure Help
PM
Please respond
to cf-talk
Just like a cfquery, you need to reference the query:
<cfoutput query="test">
!#prequal_num#! - !#borrower_lastname#! - !#highest_status#! - !
#comment_exists#!<br>
</cfoutput>
or
<cfoutput>
!#test.prequal_num#! - !#test.borrower_lastname#! - !#test.highest_status#!
- !
#test.comment_exists#!<br>
</cfoutput>
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 10, 2000 2:07 PM
To: [EMAIL PROTECTED]
Subject: Stored Procedure Help
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.
------------------------------------------------------------------------------
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.
------------------------------------------------------------------------------
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.
