The marine part of AvP, and most of SS2 were pretty much built around making you scared witless thorugh your enemies, the gampelay, and the environment. They were, to me, just beyond awesome in the way they got you to feel scared. I wouldn't call it horror, though, it's more like extreme suspense.
The RE style fear through crap controls, on the other hand seems just lazy design to me. As for F.E.A.R., while I enjoyed its fights immensely, I thought that the scary bits only made the game feel broken and disjointed, and never managed to scare me at all. Except for the bit with the ladder. That was a nice trick.
This review made me want to give Condemned a go.