1CREATE PROCEDURE dbo.spHouses_GetNearZipcode
2
3@Zipcode char(5),
4@Miles decimal(11,6)
5
6AS
7
8--Load close zipcodes into temp table
9SELECT ZIP.ZipCode, ZIP.City, 
10	dbo.DistanceFunc(ZIP.Latitude, ZIP.Longitude, RAD.Latitude, RAD.Longitude) As Distance
11	INTO #TempZips
12FROM ZipCodes ZIP, RadiusFunc(@ZipCode, @Miles) RAD
13WHERE (ZIP.Latitude BETWEEN RAD.MinLatitude AND RAD.MaxLatitude) AND
14	(ZIP.Longitude BETWEEN RAD.MinLongitude AND RAD.MaxLongitude) AND
15	(dbo.DistanceFunc(ZIP.Latitude,ZIP.Longitude,RAD.Latitude,RAD.Longitude) <= @Miles)
16
17--Search Houses table and JOIN to temp zipcodes table
18SELECT H.*, Zips.Distance AS Miles
19FROM Houses H INNER JOIN 
20	#TempZips Zips ON Zips.ZipCode = H.ZipCode
21ORDER BY Zips.Distance, H.Price
22
23--Drop temp zipcodes table
24DROP TABLE #TempZips
25
26GO