Fallout 3, Fallout NV (Fallout 3 has a better story)
Fable III is a more casual story but its not too terrible.
Fable Lost Chapters is like that game that comes free with your HP pc (Fate or something) story is not too good, but the game is pretty laid back.
Dishonored Great plot twists
Tombraider 2013 (pretty good, one of my top 10)
Deus Ex Human Revolution (multiple play throughs, with different aspects of the story line)
Dragon Age Origins, Dragon age Awakening (as @beyondlogic pointed out) are great stories
Highly recommend Oblivion and SKyrim if you like Questing Games
WItcher 2 assasins of kings (pretty good story, slightly ifffy gameplay)
BIOSHOCK: Infinite has a very great story
Mass Effect Series: have very interesting stories, although in ME2 it got predictable sometimes... and you can get lost in the long dungeons sometimes....
Half Life series (and the episodes) pretty great story, but its mostly an FPS than a story game
Sleeping Dogs (a one off GTA game about asian guy) story is slightly worse than GTA IV
GTA IV and Episodes from Liberty City: not too terrible, but its main value is in the ability to just explore an open world.
Star Wars KOTR has a pretty good Star Wars themed story, slightly simmilar style as ME1
Just Cause: Terrible story, but you get to go on massive killing sprees....
Left 4 dead series: very cinematic FPS survival game... its mostly an FPS than a story game (99.9% of people dont like the story, but it is a great massive shoot everything that moves game)
Metro 2033 and Metro Last Light I absolutely loved the Metro 2033 game and the story it presented. if you take good choices or bad, you will have different outcomes. i havent played Metro Last Light, but it seems to be just as good. (its one of the few FPS games that have a story that rivals its shooter qualities)
Portal and Portal 2 both are awesome puzzlers with a good story to it. not very intense and slightly laid back games
I would say that the "Play these games at least once" list would be: portal, bioshock infinite, witcher, deus ex, mass effect (1,2,3), fallout 3, fallout NV, Oblivion, Skyrim, Tomb Raider, Dishonored, and Metro 2033.