I was working on some queries that used SQL Spatial data types and ran into a problem where STIntersects function was returning NULL. I was expecting it to say 1 or 0, but it kept on returning NULL. Here’s how I solved it:

1. On Technet article for STIntersects, it said “This method always returns null if the spatial reference IDs (SRIDs) of the geometry instances do not match.“

here’s my pseudo code for you:
– – Function
– – Input Parameters @Area Geometry, @Point1 varchar(10), @Point2 varchar(10)
DECLARE @p point;
SET @p = geometry::STGeomFromText(‘POINT(@Point1 @Point2)’, 0);
– -Note that the 0 in the above line is SRID
return(SELECT @p.STIntersects(@area));

2. So next step was to make sure that the two geometry instances that I was using had same SRID’s.

3. Since I was passing the @area (geometry data type) to the function, I had to check the SRID for @area.
so wrote a single line of code that used the STSrid function.

Select @area.STSrid

That told me that the SRID of the geometric data type was 4xxx

4. So now I modified the original code to change the SRID of @p variable to same as that of @area variable.

So here’s the updated code:
DECLARE @p point;
SET @p = geometry::STGeomFromText(‘POINT(@Point1 @Point2)’, 4xxx);
return(SELECT @p.STIntersects(@area));

5. Now since the SRID of @area and @p matched, it started returning 1 or 0 values as expected.