Here is a link to a blog by Isaac Kunen discussing "nearest neighbor" solutions using spatial functions in SQL 2008. (This query will actually become fairly easy in SQL 2012.)
http://blogs.msdn.com/b/isaac/archive/2008/10/23/nearest-neighbors.aspx
Edited to drop a lot of nonsense.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills