It's been stated before, but really, Silent Hill man. Silent freaking Hill. I've played all but 4 and Origins and they are awesomely atmospheric, intense, and surprising in how they go. Homecoming not so much, but the less said about that the better...

Also, Siren is a really freaky time. Being defenseless or having limited ammo and having to sneak around while Sightjacking enemies to try and keep tabs them really gets you tense, making that person behind you with a pitchfork even more startling. Especially if you happen to Sightjack them and just see yourself from their eyes. Bloody brilliant and terrifying at night.

On the other end of the spectrum, the original Resident Evil games scare you not with a sense of dread and atmosphere, but startle you with dogs through windows and camera angles that leave you walking straight into the undead. Not the same style of horror and not my preferred one, but fun nonetheless.