Section C.4. Step 3: Create the FindNearby Stored Procedure

C.4. Step 3: Create the FindNearby Stored Procedure

Create a SQL stored procedure called FindNearby:

     CREATE PROCEDURE FindNearby        @CenterLat float,        @CenterLon float,        @SearchDistance float,        @Units int AS     declare @CntXAxis float     declare @CntYAxis float     declare @CntZAxis float     declare @EarthRadius float     -- Miles = 0     if(@Units = 0)     set @EarthRadius = 3963.0     else     set @EarthRadius = 6378.5     set @CntXAxis = cos(radians(@CenterLat)) * cos(radians(@CenterLon))     set @CntYAxis = cos(radians(@CenterLat)) * sin(radians(@CenterLon))     set @CntZAxis = sin(radians(@CenterLat))     select BusinessEntity.ID,            BusinessEntity.Name,            ProxDistance = @EarthRadius * acos( XAxis*@CntXAxis +                           YAxis*@CntYAxis + ZAxis*@CntZAxis)     from  [BusinessEntity]     where  @EarthRadius * acos( XAxis*@CntXAxis + YAxis*@CntYAxis +            ZAxis*@CntZAxis) <= @SearchDistance     order by ProxDistance ASC

Now you can execute FindNearby queries using your own SQL implementation.