I can't believe no one has mentioned Planescape: Torment. That's the hands-down winner for me. Off the top of my head, roughly in order:
Planescape: Torment
BioShock
Shadow of the Colossus
Shin Megami Tensei: Digital Devil Saga 1 & 2
Baldur's Gate
ICO
Portal
Okami
Killer7
WarCraft III
Psychonauts
Honourable mention for a series I didn't finish but that had a fantastic story: Marathon
[Added: Also, I've never played HL or HL2, but now that I have the Orange Box I'm looking forward to HL2+Ep1+Ep2.]
I'm playing The Darkness right now and I've really enjoyed the story so far. I have about 1.5 chapters left in the game, though.
Planescape: Torment
BioShock
Shadow of the Colossus
Shin Megami Tensei: Digital Devil Saga 1 & 2
Baldur's Gate
ICO
Portal
Okami
Killer7
WarCraft III
Psychonauts
Honourable mention for a series I didn't finish but that had a fantastic story: Marathon
[Added: Also, I've never played HL or HL2, but now that I have the Orange Box I'm looking forward to HL2+Ep1+Ep2.]
I'm playing The Darkness right now and I've really enjoyed the story so far. I have about 1.5 chapters left in the game, though.