A few examples of good stories:
* Half-Life does not have a really complicated story, but it's good enough to turn mindless running around corridors (Quake II-style) into achieving actual goals.
* Bioshock Infinite has an insanely great story with an awesome ending. Forget the graphics (not bad at all), forget the gameplay (also quite entertaining), the story is probably the best in history of gaming. This game will definitely be remembered.
And bad ones:
* Unreal II: The Awakening has a terrible story and dialogue. But graphics were great and gameplay was OK (typical for FPS developed during that time). Probably nobody remembers this game now (except for how bad the dialogue was).
* Unreal Tournament, Quake III have absolutely no story in single-player. It seems nobody played single-player at all, or only used it to train for multi-player deathmatches.
Some gameplay types do not need a story, and sadly it seems that this includes most modern games, such as free-to-play timekillers (no need for a story when the purpose is grinding for coins) and multiplayer games where the any sort of story interferes with the gameplay.