I am pretty much of the view that the two 'giants' of horror rpgs are still Call of Cthulhu and Vampire: The Masquerade. Yet they are entirely different games to play.
In Call of Cthulhu, the characters are essentially outmatched by the forces they face and vulnerable. Yet the horror they investigate is ephemeral for the most part - hidden out of sight and only hinted at in ancient writings and in the 'dark corners of the world'. Moreover, the world view represents is pretty conservative in outlook - xenophobic, fearful of the future, clinging to an antiquated past and so on - whilst at the same time being coldly scientific and uncaring.
Vampire: the Masquerade, on the other hand has you playing empowered, romanticised beings (who are nevertheless vulnerable to other vampires and supernatural creatures). Their major challenges come from both themselves (the personal horror of being a monster) and other supernatural creatures of the night. As such, it tends to be more left of centre, politically speaking, casting PCs as freshly 'embraced' young turks surviving against the older, more powerful generations of vampires.
Other horror games I like include Kult (still the visceral horror setting, based on a darkly plausible gnostic world view), Unknown Armies (which combines supernatural 'post-modernism' with a Coens brother-like cinematic quality), and modern iterations of the Cthulhu mythos - Delta Green and The Laundry.
Finally, although it's lighter in tone and more family orientated, Doctor Who provides a perfect vehicle for weird, sci-fi/horror scenarios. Just imagine you got the green light for your scenario to make an 'grown-up' episode...