I rather enjoyed Fallout 3's plot, actually.
The first season of the Walking Dead game had me glued to the screen for a good long while (haven't got around to Season Two yet, though.)
I'll also second KOTOR - that game gave me quite a few surprises and the internal narrative I'd built up never "broke" no matter how much I pushed at it. (My character had a whole arc going, as it turned out - started as a selfish rogue, argued with all my companions constantly, tried as hard as the game would let me to avoid becoming a Jedi... Then Bastilla and I had a "I hate you, I hate you, I... love you" moment, she taught me to love and brought me over to the light side of the force, and so on.)
Papers, Please has a rather clever story, I thought, and tied in quite well with it's game mechanics.
Another one I've been kind of partial to has been the Prince of Persia games - kind of nice little stories, if a little arch. But it all wraps up nicely in the third game with a fitting time-travelling twist at the end.