Fate/Stay Night (especially the Heaven's Feel route) and Tsukihime (pretty much all the routes, except the Hisui route is pretty fucking creepy and more psychological... since you don't really move around too often).
I think the best horror is unintended ones though. Like the times you run completely out of ammo (or barely any), no health, and you're either being chased by a shit load of enemies, trying to stealth your way out of a huge fortification, or best, you're in the middle of a desolate dungeon with corpses everywhere, and now you KNOW there's a few more threats, but you just don't know where.
BioShock on normal (gonna try Hard, then Survivalist, which I'm really anticipating) kinda has the mood killed by the respawning enemies (I think it's bad. NIGHTMARE mode in DooM is not only too hard for me *laugh*, but makes it feel more like an fast action game. A horror game should be slow, even if slow is easier than trying to rush through a level in NIGHTMARE) and the action oriented gameplay.
Good horror is:
Stealth Gameplay.
Limited Ammo
Psychological (no bullshit screamers/pop ups like in the Grudge movie, or as I hear it, DooM 3. The fear should be generated by both you, and the feeling should be of disgust and taboo. Uncanny valley, having small weirdness, etc. It's the feeling of everything being normal, EXCEPT for a little something.)
Being weaker than the enemy
Creativity based (you can create traps, or use anything)
Open ended maps
EXTRA:
Having no humans, but a few occasional ones. (Going completely monster makes it predictable, but having too many humans kills the "something's wrong" feeling. Perfect example is the underwater sea levels in Deus Ex).
