Spatial query: A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":

Question

I am trying to create a parameterized stored procedure from a spatial query (SQL Server 2016). The underlying spatial query works fine when the parameter (@long/ longitude) is hard coded (e.g 174.7115) .

When I attempt to create a Stored Proc with a parameter for longitude (@long) I get the following error.

Msg 6522, Level 16, State 1, Procedure Spatial8, Line 5 [Batch Start Line 0] A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": System.FormatException: 24141: A number is expected at position 11 of the input. The input has @Long. System.FormatException: at Microsoft.SqlServer.Types.WellKnownTextReader.RecognizeDouble() at Microsoft.SqlServer.Types.WellKnownTextReader.ParsePointText(Boolean parseParentheses) at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type) at Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid) at Microsoft.SqlServer.Types.SqlGeography.ParseText(OpenGisType type, SqlChars taggedText, Int32 srid) at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid) .........................

Here is the Stored Proc..

CREATE PROC Spatial8 @Long decimal(9,6)
AS
DECLARE @Car geography;
SET @Car = geography::STGeomFromText ('Point(@Long -36.81143)', 4326);

/* Add 20m buffer to each side of the cars position (Lat and long) */
DECLARE @Pointbuffer geography;
SET @Pointbuffer = @Car.STBuffer ('20');

Select *, @Pointbuffer.STContains(geography ::Point(Latitude, Longitude, 4326 )) As PointBuffer
From dbo.Location
WHERE @Pointbuffer.STContains(geography ::Point(Latitude, Longitude, 4326 )) = 1

Any advice, suggestions, or work arounds would be appreciated. I have tried swapping Geography to Geometry, but I still get the same error.

Answer 1

SQL Server will not substitute the parameter by itself in the geometry tagged text.

Create a @geometry_tagged_text variable of type NVARCHAR(MAX), format it as below and pass that parameter to geography::STGeomFromText.

DECLARE @geometry_tagged_text NVARCHAR(MAX);
SET @geometry_tagged_text=N'Point('+CAST(@Long AS NVARCHAR)+N' -36.81143)';

DECLARE @Car geography;
SET @Car = geography::STGeomFromText (@geometry_tagged_text, 4326);