Rick, Did you take a look at the query that I sent earlier? It takes only 3 seconds to run on my machine.
Russ > -----Original Message----- > From: Rick Root [mailto:[EMAIL PROTECTED] > Sent: Tuesday, February 06, 2007 11:49 AM > To: CF-Talk > Subject: OT: Transact-SQL help > > I've got my zip code database proximity thing all figured out. I have a > nice getdistance function that calcs the distance between two zip codes. > > syntax of my function: getDistance(zip1,long1,lat1,zip2,long2,lat2) > taking either the zip code or the lat/long for each... > > I can now do: > > SELECT * > FROM prospects A > WHERE > zipcode in > ( > SELECT B.zipcode > FROM zipcodes B > WHERE > getDistance('27502',0.0,0.0,'',B.latitude,B.longitude) > ) > > However, that is very slow. It's very fast if I pass in the lat and long. > > So I've written a stored procedure that when executed looks like this: > > sp_zipcodes '27502' 25 > > It returns a result set containing all the zip codes within 25 miles of > 27502. > > But I can't use that in an IN clause... so what do I do? > > Rick > > -- > I'm not certified, but I have been told that I'm certifiable... > Visit http://www.opensourcecf.com today! > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268844 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
