USE MyDatabase /* Substitute your database name. */
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BizLocator]
( @Zipcode nvarchar(5) = '',
@City varchar(128) = '',
@State nvarchar(2) = '',
@MaxDisplay int = 5,
@MaxDistance int = 200
)
AS
DECLARE @Lat float
DECLARE @Lon float
DECLARE @ToRad float
SET @ToRad = pi() / 180.0
IF @State = '0' OR @State = ''
BEGIN
SELECT @Lon = Longitude, @Lat = Latitude FROM Zipcodes WHERE Zipcode = @Zipcode
END
ELSE
BEGIN
SELECT @Lon = Longitude, @Lat = Latitude FROM Zipcodes WHERE City = @City AND Abbr = @State
END
CREATE TABLE #TempBiz(
[ID] [int] NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[Adr1] [nvarchar](max) NOT NULL,
[Adr2] [nvarchar](max) NULL,
[City] [nvarchar](max) NULL,
[State] [nchar](2) NULL,
[ZIP] [nchar](5) NULL,
[AREA CODE] [nchar](3) NULL,
[PHONE NO] [nchar](7) NULL,
[A] [float] NULL,
[B] [float] NULL,
[C] [float] NULL
) ON [PRIMARY]
INSERT INTO #TempBiz (ID, Name, Adr1, Adr2, City, State, ZIP, [Area Code], [Phone No], A, B, C)
SELECT biz.*,
(sin(@Lat * @ToRad) * sin(zip.latitude * @ToRad) ) AS A,
(cos(@Lat * @ToRad) * cos(zip.latitude * @ToRad)) AS B,
(cos ( (@Lon - zip.longitude) * @ToRad) ) AS C
FROM BizLocations biz
JOIN ZipCodes zip
ON (zip.zipcode = biz.zip)
SET ROWCOUNT @MaxDisplay
SELECT *, (3951.1329 * acos(A + B * C) ) AS Distance
FROM #TempBiz
WHERE (3951.1329 * acos(A + B * C) ) < @MaxDistance
ORDER BY Distance
DROP TABLE #TempBiz
RETURN @Lat
|