CREATE PROCEDURE dbo.sp_RosterPropertyRadiusSearch
@SourceZipCode varchar(5),
@MaxMiles int

AS

DECLARE @SourceLatitudeRadians float
SET @SourceLatitudeRadians = (SELECT LatitudeRadians FROM Zipcodes WHERE ZipCode = @SourceZipCode AND PrimaryRecord = 'P')

DECLARE @SourceLongitudeRadians float
SET @SourceLongitudeRadians = (SELECT LongitudeRadians FROM Zipcodes WHERE ZipCode = @SourceZipCode AND PrimaryRecord = 'P')

SELECT PropID, 
       Property, 
       City, 
       State, 
       Zip, 
       Miles = ACOS(COS(@SourceLatitudeRadians) * COS(@SourceLongitudeRadians) * COS(LatitudeRadians) * COS(LongitudeRadians) + COS(@SourceLatitudeRadians) * SIN(@SourceLongitudeRadians) * COS(LatitudeRadians) * SIN(LongitudeRadians) + SIN(@SourceLatitudeRadians) * SIN(LatitudeRadians)) * 3963.1,
       Kilometers = (ACOS(COS(@SourceLatitudeRadians) * COS(@SourceLongitudeRadians) * COS(LatitudeRadians) * COS(LongitudeRadians) + COS(@SourceLatitudeRadians) * SIN(@SourceLongitudeRadians) * COS(LatitudeRadians) * SIN(LongitudeRadians) + SIN(@SourceLatitudeRadians) * SIN(LatitudeRadians)) * 3963.1) * 1.6
FROM   vu_PropertyCoordinates
WHERE  (ACOS(COS(@SourceLatitudeRadians) * COS(@SourceLongitudeRadians) * COS(LatitudeRadians) * COS(LongitudeRadians) + COS(@SourceLatitudeRadians) * SIN(@SourceLongitudeRadians) * COS(LatitudeRadians) * SIN(LongitudeRadians) + SIN(@SourceLatitudeRadians) * SIN(LatitudeRadians)) * 3963.1) < @MaxMiles
ORDER BY Miles, Property, City, State, Zip
GO