SQL Server 2008 Spatial: find a point in polygon

Question

I am using SQL Server 2008 spatial data types. I have a table with all States (as polygons) as data type GEOMETRY. Now I want to check if a point's coordinates (latitudes, longitudes) as data type GEOGRAPHY, is inside that State or not.

I could not find any example using the new spatial data types. Currently, I have a workaround which was implemented many years ago, but it has some drawbacks.

I've both SQL Server 2008 and 2012. If the new version has some enhancements, I can start working in it too.

Thanks.

UPDATE 1:

I am adding a code sample for a bit more clarity.

declare @s geometry  --GeomCol is of this type too.
declare @z geography --GeogCol is of this type too.

select @s = GeomCol
from AllStates
where STATE_ABBR = 'NY'

select @z = GeogCol
from AllZipCodes
where ZipCode = 10101

Answer 1

I think the geography method STIntersects() will do what you want:

DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326);
SET @h = geography::Point(47.653, -122.358, 4326)

SELECT @g.STIntersects(@h)