I think horror is a really, really difficult element to pull off in RPG's, and frankly it isn't a theme I particularly enjoy for the most part. I play a lot of heroic style games, and I'm sure there have been plenty of tense moments where a lot hinged on a single die roll.
But, I think I'll share instead a sort of scary but mostly funny tale. After graduating college I moved away for several years, but returned briefly to live in the same state where I attended college for about six months, and got back together with two of my old gaming group to play, of all things, Rifts. I ran the session and things were going pretty well when suddenly one of the players cracked a joke and I chuckled while at the same time eating a nacho chip.
I began choking, coughed, turned red and then, after what seemed like an eternity for me but was probably less than three seconds, proceeded to vomit right into the snack bowl. There was stunned silence for a moment and then one of the players (the one who make the joke) said quietly, "I don't know what you have planned for this evening, but nothing could be as scary as that."
Oh, I need a bit of artwork, maybe a Ramon Perez Rifts piece.