For on-the-spot scary, I'd go with Penumbra - good atmosphere and no weapons, though effective as that is, I really think that's too much of a cheap trick. The scariest is when the game really starts to mess with your mind.
Though I've never played Condemned, the only game that I think trumps even Call of Cthulhu is Pathologic. It's an open-world game where you're thrust into a sprawling, beautiful but very haunting and rather bizarre Russian city, with a brooding atmosphere.
After a few days, the disease strikes - the air tinges a sickly yellow, the afflicted roam the streets, infecting you if you get too close. Not only do you have to worry about them... you also have to scavenge for food and medical supplies to keep yourself alive. Eat the wrong sausage and you get infected. Then you enter a house, whose occupants you talked to just an hour ago, finding the walls covered in blood, the occupants writhing on the ground in agony - a fetid cloud of disease floating in the room, which then lunges at you...
It's the only game I know (with the possible exception of Call of Cthulhu) that manages to create a world with an atmosphere so vividly horrid, it plunges dread into your soul - even moreso as the premise is believable, one of those things that could happen to anyone (something which, unlike horror films, games often eschew in favour of space-stations and magical monsters).
