If a game does not have good story, then it is a poorly made husk of a game. How can you play something for just the mind-numbing action and killing. Leave that for the online multiplayer game where it belongs. Thought-provoking dialogue, creative characters and a deep, rich story is the most important part of a game, everything comes second.Over the years I have come to realize this. If it has a weak story I will drop it because it is nothing.
If you want to know some really good story based games, check these ones out. Mass Effect 1 and 2, BioShock 1 and hopefully 2, Elder Scrolls 3: Morrowind, Elder Scrolls 4: Oblivion, Fallout 3, Knights of the Old Republic 1& 2, Fable 1 & 2 and pretty much all the Final Fantasy games(except X-2 and XII, those were both pieces of dung.).
Those are some of the best ones I can think of a the moment. It pisses me off when people are going crazy over a game, 'OMG! These graphics are so awesome.' Graphics are one of the last things you should look at. It's like Avatar, it's beauty but its also a piece of crap. A piece of dog dung, wrapped in pretty packaging. A complete waste of my life and money.
Sigh, alright rant over.