Using the geospatial SQL functions you can check if a point (with a given X,Y) is included inside a polygon
Example:
Declare @point geometry
Declare @polygon geometry
SET @point = geometry::STGeomFromText('POINT (-88.22 41.50000001)', 4326)
SET @polygon = geometry::STGeomFromText('POLYGON ((-88.2 41.5, -88.2 41.6, -88.3 41.6, -88.3 41.5, -88.2 41.5))', 4326)--124
Select @point.STIntersection(@polygon).ToString()
Result:
POINT (-88.220000000670552 41.500000009313226)
References:
http://weblogs.asp.net/jhallal/archive/2011/12/27/geospatial-data-in-sql-server-2008.aspx