RE: stored procedure doesn't return anything

[Smith, Matthew P -CONT(CSC)]

SET NOCOUNT ON for the first select?



-----Original Message-----
From: Reece, Cynthia [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 12, 2003 1:51 PM
To: CF-Talk
Subject: RE: stored procedure doesn't return anything

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

Get the mailserver that powers this list at 
http://www.coolfusion.com