Hmmm....
See the problem here is whether I side on my 'these are my fav games and therefore have great stories' or 'these games truly have excellent narratives.'
I'll go with the latter, and if my fav games end up in the list of bests, than that's awesome.
Though most people won't touch it, the Ratchet and Clank series has a beautiful on going narrative for its entire run on the PS2. It was well executed, attached to its own gameplay, and engaging. It was also darkly funny, and, differing from other platformers of the generation, actually self-referenced itself across games. R&C almost need to be played game to game to game back to back to get the full story, but all told it is an excellent piece of writing, if simple.
Also against conventional wisdom, Final Fantasy X and X-2. People often don't realize that FFX is an incomplete game for the Final Fantasy style. In all Final Fantasy's, the world must be saved twice from two entirely different threats, who may or may not be related. The protagonist of the series is Yuna, not Tidus, reversing the normal male lead of the games. Where in most of the FF series it is a female who brings out the best in the male at the games middle, around the defeat of the first great threat, X is different, having it be the male bringing out the best in the female immediately prior to the defeat of the first big bad. His disappearance at the end of the first half then becomes the driving part of the second half. That being said, the X and X-2 saga as a whole is possibly the best written of the Final Fantasies, and is horribly underrated as a whole for good character writing, and engaging, well paced story. However, as the games were separated, and both were fairly painful alone, this is often over looked.
Though both Starcraft and Warcraft suffer horribly from what I like to call 'Why the hell does Blizzard not require every word written by Chris Metzen be fact checked back against previous works for consistency.", the Diablo series, from first game, to second game, to expansion, and from all looks of it to third game, has been dark, brooding, and brilliant. But that's important there. Too often, people confuse dark and brooding as being good writing, and dismiss light, cheerful games with far superior writing. If you played the games back when they were released, you all remember your shock and fury when Baal was revealed before the beginning of the expansion.
I think I have to make a nod towards both the Baldur's Gate computer series for great character writing, exceedingly good narrative and pacing (as long as you know where to go), and being the second series that has ever made me feel emotional pain. For those who've played it from the beginning of the first game til the end of Throne of Bhaal, I'll just say Imoen. For those who haven't.... go play!
And last, the first game to cause me emotional pain, and only in text. The Marathon Series. From Marathon, through Durandal, through Infinity, to Aleph One made Rubicon, which I consider part of the series. This series has the best story and narrative and characterizations of any series, and it had to do it all in text. I cried when Leela died. I still loathe, though respect Durandal. And if Tycho were a real person? I'd kill him on the spot.
The worst game story of all time is much simpler.
Quake 4. Go play Quake 2, enjoy it, finish it. Then go play Quake 4. Not only is it a terrible game, but the story will leave you feeling more than a little cheated.