I say it depends on the player and on your intended audience. If the story is interesting, people will love it. However you can't expect shooter fans to like your story, they don't want one. Some do - and they love Half-life, but let's be reasonable.....most shooter fans only want action.

Now try and make an adventure game or RPG without story. People get bored. Oblivion's lack of story came with an increased focus on action and thats why it sold so much. It was a huge sandbox action rpg. Storywise? It sucked mooseballs and lost many RPG fans along the way. Even bethesda admits it and wants Fallout to have a more linear plot versus just "do what you want".

Story principles are not the same for every game. Mario? Fuck story, I don't care why I need to get to the flagpole, just let me jump. Sam and Max? I want a funny plotline and interesting characters. Project Origin (fear sequel)? Who cares, let me nail some folks to the wall!

Bioshock was in between. Unfortunately for me it lost in both categories a little, but ultimately was a cool game. For instance combat can be easy, due to you being overpowered early. That hurts the shooter aspect. The story was interesting and the world in depth, but they filled it with mostly zombies and left many aspects unexplored and relied on taped journals instead of other methods of storytelling.