I think Doom 3 and Doom 3 Reserrection of Evil are the scariest. I just started playing it again last week. I also think the Painkiller games are scary.
Im a bit older so the scariest I ever played had to be a time and place thing. Sitting in the back of a tour bus in the dark playing this alone. Old school now but for that time period it was by far the scariest. The First Resident evil. Since then I accidentally ripped my thumb off and became a bit disillusioned with gaming. The band I was with was called Sepultura and THEY became fans of the game. Brazilians hadn't played anything like this.