Depends, the way I see it, without a decent story most adventure games would be like watching paint dry, indeed if the story or characters don't do it for you, that's what it's like for many people.
Playing Fallout: Vegas recently, the story is pretty much non existent. But the gameplay is far less linear than Fallout 3, it's not as restricted by the story with multiple unkillable must have NPC's that can only become unconscious shrugging off vicious attacks. Being bad in the game is now a much more viable and rewarding option. I think most RPG's, do a pretty bad job of letting you be bad as you are shoe-horned into the "hero" role, as with most games. Mass Effect has a good story, but the characters and gameplay and storytelling was mostly like watching paint dry to me. Something I noticed while playing through Halflife 2: Episode 1 a few weeks from the opening is that this characters interaction emotes better than any single character in mass effect. A dish. With about 4-5 facial animations. A side character, with very little depth and no dialogue, in the game for about 5 minutes.
To me just shows how boring and sterile Mass Effect is, all the character driven gameplay and well written story becomes pretty moot when it amounts to cardboard boxes blathering overly serous pretentious dribble like it's a bloody Christopher Nolan movie.
I think Team Fortress 2 showed that a multiplayer game need not be totally devoid of characterization. It can not only be equal too, but surpass single player games in characterization. Borderlands seems to have tried but still didn't seem to get this. Whereas Team Fortress 2 is like a cartoon with characters constantly interacting with each other, Borderlands is just stylized, with no name characters. It's still sterile and relatively lifeless regardless of being highly stylized. If they added the same amount of interaction as Team Fortress 2, it probably would have been a much better game imo.
RTS games have went downhill. Homeworld long before Starcraft II showed a RTS need not be some boring top down game with no story or cinematic flair. But that's what most of them have become. That or action/RPG hybrids. With story-telling counting as fluff. Which I would guess is why a lot of people don't bother with RTS games.
When racing games try to push some character, they tend to become annoying. Dirt 2 attempted to be "americany" with fellows shouting "yo, dude, this track is totally rad" when maps would load up. It just came across as cheese. F12010 also done it. I prefer my racers sterile like Gran Turismo.
I think some games (but really just Call Of Duty) are far too reliant on scripted sequences as a means of story-telling. The entire game, just becomes a big scripted sequence. With almost everything surrounding it, mediocre or dated. I made a video a few months back showing it side by side with a now 6 year old game.
http://www.youtube.com/watch?v=AXRo7ZTZAKg
You hear people say "oh well, Call Of Duty is about the multiplayer".
If that's the case, don't bother making a single player game. Other games don't have this problem. Why should Call Of Duty be excused?