it largely depends on the genre, as you have inadvertently pointed out already. a puzzle game obviously doesn't need a story to be good. the gameplay just simply needs to be addictive, simple, and somewhat forgiving.
then, you can step up to a shooter. most shooters depend on at least a simple story to move the action forward. although the action is the main attraction, a good story does a lot to improve the overall feel of the game. the Half Life games are an excellent example of this.
if you move it yet another step forward to an RPG, then you're really talking about story-driven gameplay. the story itself is central to everything, and thus a poor story will usually result in a poor game. however, its still not the most central component of an RPG as a pretty good story can still be used to compliment excellent gameplay (Oblivion's main quest comes to mind here). still, even an RPG with excellent features and gameplay will no doubtedly feel inferior if it is coupled with a poor or subpar story.
in the end, i think that a storyline is generally fairly important for every game. think about
: even the first video games had some kind of a story as a backdrop, and that provided them with a nice flair to them. IMHO, i believe that a good story does much to make a game more memorable and enjoyable, but if i had to choose between storyline and gameplay, i'd go with gameplay.
just my two pennies.