I don't think every game needs to stand on single-player alone, but there definitely individual games that do. The biggest problem I have, however, is when MP is shoehorned into games that don't need it. Bioshock 2 is a prime example. Who looked at the perfectly-paced, story-driven, methodical, haunting, brilliant masterpiece that was the first Bioshock and thought "Ya know what that needs? Twitchy idiots swearing at each other over headsets! Yeah!" Oh that's right, the one who looked at the CoD sales figures. Assassin's Creed: Brotherhood is another example. I never played it so I can't comment on the quality of the MP or if SP suffered, but I did love AC2 and there's no way in hell that game needed a MP component. At least they tried to be original about it.
The latest example of this is God of War: Ascension. Seriously, who in the flying mother of fuck thought the GoW series needed MP? At least Bioshock's story was setting-based instead of character-based so it didn't feel TOO weird being put into the shoes of a run-of-the-mill splicer (yeah, I know the main character was important, but he was still a blank slate: Rapture was the star of the game). GoW, on the other hand? Playing as Kratos IS GoW! The point of the series isn't just to explore ancient Greece, or kill Minotaurs, or even curb-stomp the Greek Gods, it's doing all that as demi-god one-man wrecking crew Kratos. You empathize with him (to a point), you guide him through the game, but in the end the satisfaction comes from seeing Kratos work out his daddy issues in the most awe-inspiringly brutal fashion imaginable. Taking you out of his shoes (or sandals, whatever) and making you a faceless nobody destroys what makes the series so enjoyable and turns it into another generic hacky-slashy-stabby gorefest. I'm not saying the game won't be good, but if the SP suffers a dip in quality because the crew allocated valuable resources to the most pointless MP mode since Ratchet and Clank: Up Your Arsenal I'm gonna be so pissed. Like, charge up the side of Mount Olympus on the back of a rampaging Titan to introduce the King of the Gods to the business end of my blade pissed.