The first couple of RE I think re3 was the scariest
Dead space series...although 3 was not scary
I aslo suggest Condemned I think thats on PS3
I played it on the 360 and yeah, while it's not scary imo, it's a close to palm sweating survival horror as you're going to get. I have yet to play a game that 'scared' me. I want to though. There's supposed to be one coming out next gen that relies on darkness and using a camcorder night vision for visibility. Read about it in GameInformer. Looks promising.
Honestly, not really. The horror genre on consoles has been going through a dead period. The good news is, if you have a PS2 or a PS3 that can play PS2 games, the PS2 and PSOne has penty of great horror titles to choose from. I'll give you a short list of some of my favorites:
Silent Hill 1-3
Resident Evil 1-3
Fatal Frame 1-3
Siren/Siren: Blood Curse
Those are a few of my favorites. If you have to play something next gen, really the only games I can recommend in the horror genre would be F.E.A.R. and the original Dead Space (hated 2 and 3). I'd say that the above list outclasses both of those games though. Hope I helped :-).