Sanitarium, very emotional, good writing and the pacing is excellent.
I typically despise most RPG storylines (if not all of them) because they nearly always tend to follow the typical RPG mistake of being too wordy.
There's a talent in being able to create a concise narrative that carries the story and manages to emotionally bind the player to the game at a very slight degree.
Max Payne 2 did this well as far as stories in gaming go generally, the comic book panels weren't as long as the original game's, the cinematics were typically short and non-obtrusive, once again, unlike the first game's, and most importantly, the writing didn't make you want to kill yourself, both because it was intentionally over the top, and because it somehow made you care for the characters without them spouting ten page monologues, the few neo-noir inspired quips from Max were enough to carry the character, and when the comic book panels came, they still didn't compose pages of written work.
Baldur's Gate 2, Planescape: Torment, Vampire The Masquerade: Bloodlines, Arcanum, I hate all their stories, not because of the general plot or the twists therein but because they tend to drag on forever with dialog and written segments without actually realizing that good writing is writing that is both written well and concisely without losing the depth. Torment failed the absolute worst in this regard, horrendous bucket-loads of purple-prose, incredibly obtuse writing - some of the characters were so bad that I got an incredibly bad college creative writing vibe - and the fact that it never seems to get to the point. However, the greatest crime was the fact that often these giant branching bits of dialog did nothing but establish absolutely tiny little bits of character development. While Dak'kon's dialog was probably the least cringe inducing and actually made me enjoy myself, the Ring of Zerthimon was extraordinarily wordy without holding much depth at all.
You'd read these gigantic stories, go through pages of dialog with Dak'kon, only to find out something base like "Dak'kon feels enslaved" or whatever the balls they wanted to point out.
I don't expect something on the level of Joyce in a game, but really, stylized contained writing in a well paced shell is far more appreciable than overwrought bags of one thousand word count text blocks.
On the opposite spectrum, the games that do incredibly well are ones such as Sanitarium which I mentioned at first. The dialog doesn't carry on forever, the characters are all interesting, well made and avoid being annoying.
Another good aspect regarding Sanitarium is the fact that the story moves along at a decent clip. It doesn't waver too long on insignificant plot points but it doesn't slide all about them like glossing over a SparkNotes page.
At the absolute minimalist end are games such as the first two Silent Hills, which have sparse dialog, exposition that is delivered in tiny notes, and only a few separate cutscenes that carry the story forward. I also like this style of storytelling, because it leaves it up to the player to explore and actively uncover bits of it. This is the kind of non-linear storytelling I prefer. It doesn't "come upon you" as it does in most RPGs, where you simply wander around, accomplish some tasks, then have bits of story unveiled like some sort of written prize, rather it encourages you to explore, read things you come across, examine things in the environment, and make your own conclusions.
Admittedly, the original Silent Hill had a very simplistic storyline that the rabid SH fans have completely over analyzed to the point that most of what they say is actually considered canon to many people. In reality, it's just a shortly summarized tale that is just so unusual that it comes off as far more complex than it really is.
And that's a virtue, Team Silent managed to put a lot of subtext into a very minimalist storyline, and that takes a good amount of talent to pull off.
Talent which I would say, to the chagrin of many people, are lost on most RPG story writers, the worst offenders of which are those godawful jRPGs that I've hardly ever touched in my lifetime.