It seems to me that RPGs are somewhat antithetical to horror, beacause they're usually power fantasies. They're mostly about getting stronger, crushing your enemies, and coming out a winner, with a bigger dick and a hot girl stroking it.
So you're usually only going to find action RPGs dealing with horror as an atmospheric thing, a horror you'll succeed in pushing back by fighting. Even Diablo 1 with its bittersweet ending is like that. Horror is limited to the setting flavor.
Stygian had potential; and other games focussing on psychological horrors and sanity are interesting, but I'm not sure they succeed that well as cRPGs. There's always this distinction between making you, the player, scared, versus making the character scared and you dealing with the consequences of his insanity. FPSes and other first person games do the scaring the player better, but it's often cheap. In RPGs, the distance between player and character kind of blunts the horror. Quality of writing is more important then, and sanity or coping mechanics.