I think the most obvious answer to the question at hand is "Depends on the genre".
Indeed, sports games don't need a story. Racing games don't need a story (maybe a little background bio for each racer to justify their vehicle/racing style). RPG's obviously need a story because that's really all they are: a story you play through.
Shooters and Puzzles are really the only genres that I can see a grey area in. I mean it really doesn't matter WHY you're mowing down endless hordes of (fill in the blank), because you're going to be shooting them regardless. However personally, I do like having a bit of motivation. I like to know WHY I'm shooting these guys. Puzzle games are the same way. The biggest example I can think of is the Portal series. Portal 1 had a very faint story. All you know is that you woke up in a lab and you're being guided through a mouse maze of deadly traps. You get the sense that the computerized voice accompanying you is actively trying to get you killed, but it literally isn't the very end of the game that you get the story: AI went mad and filled the lab with nerve gas, that's why the place is "abandoned". But the game isn't about that, it's about the puzzles: literally moving from one room to the next just trying to figure out how you get to the next.
Portal 2, on the other hand, injected a lot more story into the game. Personally I enjoyed it, but I love a good story in a game. I can see how others didn't like it, though. It was a big shift from the simplicity of "Here's some puzzles: do'em while we have a dark-humor spewing AI talking to you" to all of a sudden learning the history of the characters and of the science facility. That said, however, the game that people fell in love with (Portal) was pretty much just 20 different puzzles with a puzzle-based boss fight at the end, showing that the story itself wasn't what made the game so popular.
So yeah, like I said: depends on the genre of the game.