there is already a thread for scary games, i think.
anyway, for the sake of scaryness, current one of mine is:
Silent Hill 4 (never seen any of the serie apart the jap localized one on GBA; i've played it the first time yesterday at my friend place, i cannot unplug from it for 6 hours! GREAT)
the usual ones are:
Eye of the Beholder 1 and 2
(Hired Guns is ok too)
oh yeah, if you are in the gothic kind of novel, the best ever horror game i've played is Anchorhead