The Chzo series by Ben "Yahtzee" Croshaw do this (yes, the Zero Punctuation dood). They are adventure/point-and-click style games with horror themes. You can get them free
from his site. The order is 5 Days a Stranger, 7 Days a Skeptic, Trilby's Notes, and 6 Days a Sacrifice. Pretty good for an amateur, with the third one being my favorite.
Another free game is
Eversion. It's a platformer in the same vein of a Sega Master System 8bit cutesy sidescroller, but there's more to the game.
An older adventure game called
Shivers would probably count, as would its sequel.
Another old adventure game that stars Christopher Walken - and therefore fully awesome - is
Ripper, which is a futuristic cybernetic take on the Jack the Ripper mythos. You're a detective tracking down a cyber killer, who loves to rip his victims to shreds while they are still alive. Uses full motion video for full-on Walken goodness.
All the token horror series - Silent Hill, Fatal Frame, etc. Resident Evil to a small extent. Clocktower series. Laura Bow from back in the day. I haven't played some of the other horror games like Haunting Ground to know if it counts.
The old FPS game "Clive Barker's Undying" was supposed to do this sort of thing.
Braid could be argued as doing this, depending on how invested in the story you want to be.
Potentially the Ico/Shadow of Colossus series, again depending on how invested you become.
Killer 7? I haven't personally played it. And with that note, the Flower, Sun and Rain game on the DS (also from Suda 51).
I'm pretty sure there was a Sherlock Holmes game recently that was more survival horror/Cthulu themed that would count. Come to think of it, I'm sure there's a Cthulu game or two that would count.
I'm sure there's more I'm not thinking about right now.