Well, depend, I think that a good horror game need to have shitload of suspense. Here some game that scared me more or less
-Doom 3, at first is a bit scary when you walk around and then zombie appear out of no where, but later seriously it's turn into a simple FPS. No longer scary.
-Condemned, damm that series is awesome, good story, scary place, etc...
-Nightmare House (HL2 mod) Even if this is only a mod, damm if you play for the first time alone in the dark and with light off, you gonna shit brick.