Let's see now, I'm going to list some of my favorite stories and story executions in games.
Dragon Quest V - DS version (maybe PS2 too haven't played that one yet) - The way it portrays the weight and passage of time is absolutely unlike any other game, we hadn't seen it before(tho Grim Fandango did come close), we haven't seen it so masterfully portrayed since. And thanks to the addition of party chat, the DS and PS2 versions have incredible character development and world building too. This is a game with exceptionally powerful moments about love, family, friendship bonds.
Terranigma - I care more about characters than basic plots tho, but in some cases the basic premise was so powerful I consider it among the best stories even if there wasn't much character development. That's Terranigma for me. Being all alone in a sleeping, dormant world and watching it slowly awaken is a once in a lifetime gaming experience, I doubt anyone could exectue it so well today.
Mother 3 - I played it when it first got fully translated and barely remember it anymore, but the atmosphere, powerful emotions, sense of awe that never left me throughut the game I remember well from beginning to end. It was strange, weird, surreal yet so emotionally powerful and gripping.
It's a story that's not afraid to explore many uncomfortable themes rather openly.
Clair Obscru - my best friend is going to hate me for this, but to me, E33 is one of the greatest, most powerful stories told in gaming. It's a story about unimaginable love and loss, family bonds and the meaning of life and sentience. It has some of the most easy to relate to and love characters I've ever seen in gaming, beautiful writing and voice acting. They felt like genuine, real people and...living beings I grew to love, adore, care about. We saw some seriously powerful moments in this one, facial expressions that showed so much love and pain.
Grim Fandango - Another masterpiece where the passage of time is presented wonderfully. Probably the only point and click style game I've played multiple times and still look forward to replaying again someday. From the offices with their weird computers to the casino, the city and the docks, it all felt so nostalgic, so familiar. The characters even tho you generally meet them for brief periods of time are so well written and acted, that you can't help but relate to them.
It genuinely makes you feel like you're experiencing the afterlife, I'm beyond fascinated with this one.
There's more but I'll leave it here for now