Dunno about Final Fantasy, seeing as the last one of those I played was 8 (Completely turned me off the series, however let's not go there!), but as far as I've seen the quality of rpg story seems pretty consistent to me.
Even tossing aside the super-obvious Bioware answers, there's Neverwinter Nights 2: Mask of the Betrayer which had an incredibly well done plot. Best use of the Forgotten Realms setting since Baldur's Gate 2 I'd say! Hell, the plot was the best thing that game had going for it. Then we've got The Witcher. Story starts off slow, but by the end of the game it gets damn good, and the way your every action seems to have consequences later in the plot makes it feel all the more personal and involving.
Vampire: The Masquerade - Bloodlines was also insanely good. Like really, really good! Gameplay was flawed and there were more bugs on release than, well, something with lots of bugs. But the story, the characters, the dialogue and the writing was some of the best in the industry. If you've never played it you seriously should. Mind you the game was released in 2004 so I might've already gone a bit too far back, but still; insanely good!
And of course there's Bioware. Gotta love Bioware!