I think Bioshock is scary, personally. It's not technically a horror game, but with all the Splicers jumping out of the dark to kill you, and the "ghosts," I consider it to be right outside the borders of the horror genre, waving slowly with a creepy, maniacal grin on its face.
The ghosts in Bioshock are more like leftover memories, a side effect of genetic sampling. Sort of like a residual haunting except the haunting is coming from inside you and not the environment. They're good for further explaining some parts of the plot.
Ghosts entry at Bioshock Wikia
Video of ghost in bathroom
Skyrim is epic. I have spent over 200 hours on it, close to 300 at this point. I'm at level 71 and trying to max out at level 81 because I was dumb enough to want to find out the highest level you could attain in the game, ignoring the fact that once I found out I would need to hit it for myself. This, boils and ghouls, is the compulsive nature of my game playing. RPG's are my love and the death of me, because I have a deep-seated need to get and do everything I can in the game.
One side quest I was delighted to discover is called The House of Horrors. In it, you discover a house in Markarth with a reputation for being haunted. While investigating the house with an NPC, you start to hear a voice and objects fly around the home. Turns out to be a Daedric Prince, more like a demonic possession than a haunting, but it's fun nonetheless.
The House of Horrors via the Skyrim Wikia
Video of the beginning of the quest
DRAGON AGE 2
Haunted via Dragon Age Wikia
Video of Haunted quest
I told you I loved RPG's. Adventure, adventure, adventure. I also rely heavily on the game Wikia's for help through quests, finding NPC's, figuring out battle strategy, etc. In Fable 2, the ghost you run into hangs out near a statue. The ghost will be the same gender as your character, and tell you their sad story of being left at the altar, and how they would like revenge on their former love. You convince the would-be spouse to fall in love with you and have the choice to either enact revenge and reject them for the ghost, or marry them. What a lovely story for the grandkids, right?
Till Death Do Us Part via Fable Wikia
Video of the quest
THE SEVENTH GUEST
Okay, The Seventh Guest is a horror game. But one of the ghosts in this game had such a deep impact on my psyche, terrifying me so much that even now when I viewed the video I cringed and wanted to look away. Turns out, it's not as scary as I remember it. But I was what, maybe 10 or 11 when I used to play this on our PC at home? It was scary back then. The ghost I refer to is only there for a few seconds as you walk down a dark hallway, but that first time you played and didn't expect her was chilling. You know, for a 10 year old before the high tech effects we've got today. Don't judge me.
The Seventh Guest at Moby Games
Video segment of game (Ghost at about 3 minute mark, see above)