As a player and DM, I think the best system is the 'Babylon 5' system, named for the 5 year television series by Joe Michael Straczynski. That television series would have made an excellent role playing campaign. It had the perfect blend of plot and standalone adventures, lengthy plots and overall storyline arcs to make for a perfect campaign.
Here is what I consider to be the perfect blend for a campaign, based upon this model.
#1: One long overall story that is hinted at in the first adventure and concludes in the final session. This story will build slowly, with little pieces falling into place over many, many sessions. In the end, all these pieces add up to one huge turning point focused around the PCs. (The story of Babylon 5 (B5))
Example: The BBEG in the campaign is going to be an ancient red wyrm that lives in a far off land, but is interested in acquiring things from across the world to complete an artifact of unimaginable power. In the first adventure, the PCs thwart or run across one of his agents, though they do not discover who this agent worked for until much later in the campaign.
#2: Two or three long story arcs that build over time, reach a climax, and then are completed during the life of the adventure. They might overlap, or they may be sequential. They should have some relationship to the huge story arc, but should be able to be solved without ending the major story. (The Shadow War, The Earth Civil War, The Centauri War)
Example: Returning to the dragon storyline above, you might have a low level storyline where the PCs find out who the dragon is and why he is sending out agents, a second storyline where the PCs go on a series of quests to discover all the things they need to know about the artifact under construction and a third storyline where the PCs have to make their way to the lair of the beast.
#3: Many small stories that will take 2 or 3 sessions to complete. These sessions may or may not be sequential. (The Nightwatch episodes, The Episodes relating to Sheridan's attack on Zahadum, etc ...)
Examples: That low level arch discussed above (where the PCs find out who the dragon is and why he is sending out agents) might involve a series of adventuers where 1.) the PCs run across a thief working for the dragon who steals something from them, and then must be hunted down, 2.) That thief's possessions include a treasure map leading the PCs into a dungeon crawl that takes a few sessions, and 3.) The treasure indicated on the map turns out to be something that the PCs can identify as being a part of an ancient artifact.
The PCs then have to go do some research in various adventurous ways to discover what the artifact might do ...
#4: Every session has its own climax. (Each episode of B5)
In any particular adventure, there should be one thing that is built towards for the entire session, and is completely resolved by the end of the day, though it may create more issues. There could be a mini-BBEG to kill, a puzzle to solve, a person to rescue, etc ... whatever. There should just be a single, stand out, moment of victory for the PCs in every game.
If you follow that type of format, you can't lose.