If the story is terrible I can ignore it.
If the gameplay is terrible not so much.

For me story is just something that enhances the gameplay experience. A good story will elevate an 'ok' game into something I remember fondly, or motivate me to finish a game I might otherwise not.

If the story is really bad it depends upon how much I can ignore it. If it is an FPS I can easily ignore it and move on. If it is constantly barraging me with long uninteresting cut scenes then it degrades the game experience.

Now, I think atmosphere is of the utmost importance. This is an integral part of what the story creates, but goes a lot deeper and is also greatly impacted by things like visuals and music. A game like Mega Man X, which maybe has like 20 lines of dialog, does more to stimulate my imagination than 12 hours of cutsceens in FF13.