Half-Life was pretty creepy.
System Shock was very creepy. I can't wait to play System Shock 2 when I get a decent computer.
I haven't played any of the Resident Evil or Silent Hill games, but I'd like to some time.
Hands down the creepiest game I've ever played was Neil Manke's "They Hunger" mods for Half-Life (it's a trilogy). I was happy when it was announced that the third game was the last of the series because my nerves were shot from wondering when the next zombie was going to come popping out of some dark corner.
I just couldn't do it anymore.