I don't handle scary games very well - so anything that is 'horror' is where I need to have someone sitting with me as I play. Here are a few I own and enjoy though:
Parasite Eve
House of the Dead 2, Typing of the Dead
Resident Evil, 2, 3, Code: Veronica
Snatcher
Splatterhouse 3
Games as a child that terrified me but today can now play by myself

:
Castlevania series (especially Bloodlines!)
Doom
Ecco the Dolphin
Games I think that are scary but want to play:
Dark Arms: Beast Buster
Deep Fear
Vice: Project Doom
Games that I think are terrifying and will never, ever play:
Clock Tower
Silent Hill series