Mass Effect 1 & 2 & Dragon Age 2 immediately come to mind.
If you're willing to bear with the stealth mechanics Alpha Protocol is a genre mixing RPG you might enjoy.
Uncharted 2 has a fun story.
If you're more interested in storytelling over pure storyline then Half-Life 2 & Episodes 1 & 2 are a must.
If you like RTS then surprisingly, Starcraft 2 actually has a a brilliant little storyline.
Also, how old are you willing the games to be. If you don't mind playing old games then I reccommend:
Deus Ex (the thrid in the series; Human Revolution is out in August)Planescape Torment
Neverwinter Nights 1 & 2
Baldurs Gate 1 & 2
Star Wars: Knight of the Old Republic 1 & 2