RE: stored procedure doesn't return anything

[Smith, Matthew P -CONT(CSC)]

Alternatively, set the procresult to the second record set

<cfprocresult name="dr_select" resultset="2">

Here's the SP with the NOCOUNT mod; use one or the other solution:



<!-- stored procedure -->
CREATE PROCEDURE sp_doctorsearch (@zipcode INTEGER, @radius INTEGER) 
AS
        SET NOCOUNT ON

        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

        SET NOCOUNT OFF

        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

-----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

Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. 
http://www.fusionauthority.com/signup.cfm