A game where story is any more important than in a porn film (so CoD, etc. need not apply - CoD story is laughable) is only made worse by addition of multiplayer. So Fallout, for example, would just suck if you added multiplayer.
Now that said, there are single player games where story is NOT important but yet do not have multiplayer components. Those probably would benefit from such additions. Now, examples of such games...
Well, puzzle games are generally good examples. Multiplayer Tetris is fucking FUN, but games like Incredible Machine (and its heirs, like Crazy Machine), Zuma, etc. I think could really benefit from a competitive multiplayer element (i.e. where your opponent's situation gets worse when you do something). Correct me if I am wrong, but I don't think either of those has multiplayer currently.
Civilization didn't have multiplayer for a while, and now does, and I think it was a good addition, particularly with turns over e-mail (excellent idea).
I don't know if Dungeon Siege 2 & 3 have multiplayer, but if they don't, they should.
So to summarize - a game in which story is a significant enough part cannot benefit from multiplayer. This is a strong statement but I think it's true. But, a game whose story is insignificant, or separate from gameplay, could in fact benefit from multiplayer. CoD is an example of it.