At 03:51 AM 12/5/2005, you wrote: >NOTE: You should avoid using "sp_" as the prefix for user stored procedures >- it can be detrimental to performance.
The purpose of that, in this case, is use in any database without having to specifically create the procedure in each when created in the master database. I'd forgotten about mentioning that. You'd also need to grant permissions to it, a la: grant execute on master..sp_latLongDist to public ; If you're not the administrator of the database server, though, yeah - you should probably not prefix it and probably can't create it in the master database. That said, though, claiming it's detrimental to performance is wildly false. >-----Original Message----- >From: Phillip Beazley [mailto:[EMAIL PROTECTED] >Sent: 03 December 2005 16:15 >To: CF-Talk >Subject: Re: Stored Proc > >Here's a small stored procedure that does the difference from two >sets of latitude/longitude. > >create procedure dbo.sp_latLongDist @lat1 numeric, @long1 numeric, >@lat2 numeric, @long2 numeric, @units char(2) as >set nocount on ; >declare @mult numeric ; >if @units = 'km' set @mult = 1.852 else set @mult = 1.0 ; >select >sqrt(power((69.1*(@[EMAIL PROTECTED])),2)+power((69.1*(@[EMAIL >PROTECTED])*cos(@lat1/57. >3)),2))[EMAIL PROTECTED] >as distance >go > >And example usage: > >exec dbo.sp_latLongDist 34.09011, -118.4065, 42.34892, -71.04651, 'nm' ; > > >-- > >Phillip Beazley >Onvix -- Website Hosting, Development & E-commerce >Visit http://www.onvix.com/ or call 727-578-9600. > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226062 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
