Create a Stored Procedure that returns a geography type
Hi everyone, I am trying to create a stored procedure that returns some data, along with the geography type (location) from my tables in SQL. I am passing in a LocationID where the location will be matched against another table, only returning addresses that are 5, 10, 15, or 20 miles away from the location passed in. I am almost there but I am getting errors on the syntax. Here is my query: CREATE PROCEDURE [dbo].[prcGetSpatialLocation] @userDistance int Declare @distance sys.geography, @LocationID int Set @distance = (SELECT P.SpatialLocation, FROM dbo.Programs as P WHERE LocationID = @LocationID) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT H.CenterName, H.TypeofCenter, H.Address, H.City, H.State, H.ZipCode, H.PhoneNumber, H.Website, H.PatientServices, H.SpatialLocation, H.LocationID FROM [dbo].[HealthCenterAddresses] as H WHERE ((H.SpatialLocation.STDistance(@distance)) <= @userDistance) END GO What am I doing wrong???