Hello,
I am trying to run a stored procedure from coldfusion and return the
results. When i run the query in the query analyzer giving it the expected
data is works fine, but my coldfusion page says that there were no results.
I am attatching the procedure and the coldfusion code. Any suggestions as
to what is wrong or even how I could try to debug this would be great.
<!-- stored procedure -->
CREATE PROCEDURE sp_doctorsearch (@zipcode INTEGER, @radius INTEGER)
AS
SELECT d.originalID,
3959 * ACOS(
( SIN(startZip.Lat_degree / 57.3) *
SIN(endZip.Lat_degree / 57.3))
+(COS(startZip.Lat_degree / 57.3)) * COS
(endZip.Lat_degree / 57.3)
* COS((startZip.Lon_degree - endZip.Lon_degree) /
57.3)
)
AS exactDistance
INTO #tempZips
FROM
zips startZip
INNER JOIN
AESDocs.dbo.addresses d
ON d.Zip = startZip.zip,
zips endZip
WHERE endZip.zip = @zipcode
SELECT t.exactDistance, m.*,
a.institution1, a.institution2, a.address1,
a.address2, a.address3,
a.address4, a.city, a.state, a.zip, a.phone
FROM #tempZips t INNER JOIN aesdocs.dbo.members m ON t.originalID =
m.originalID
INNER JOIN aesdocs.dbo.addresses a ON t.originalID =
a.originalID
WHERE t.exactDistance <= @radius
GO
<!-- cf code -->
<cfstoredproc procedure="sp_doctorsearch" datasource="content"
dbtype="OLEDB">
<cfprocparam type="in" dbvarname="zipcode" value="#form.zip#"
cfsqltype="CF_SQL_INTEGER">
<cfprocparam type="in" dbvarname="radius" value="#form.radius#"
cfsqltype="CF_SQL_INTEGER">
<cfprocresult name = dr_select>
</cfstoredproc>
<cfoutput>#dr_select.recordcount#</cfoutput>
Thanks so much
Cynthia
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
