It depends on what type of 'horror' you're going for? For pulse-pounding tense horror, I can echo Amnesia, Penumbra: Overture and Black Plague, Condemned: Criminal Origins, System Shock 2...
For a slightly different, more psychological mind-fuckery type of horror, I can echo Silent Hill 2/3, Eternal Darkness: Sanity's Requiem, and then recommend something that apparently nobody else played or liked... Alice: Madness Returns. And, by extension, American McGee's Alice, which you'll want to play before Madness Returns. Though it's certainly a product of a by-gone era of level design.
EDIT: Also, considering the fact that practically everything can and will kill you, Demon's Souls and Dark Souls do the atmosphere really well.