There are approximately three kinds of video games that I would say exist: those that tell stories, and those that are designed purely for gameplay, and games that aim to balance the two. I would say that most video games nowadays try to balance the two, or aim for one extreme.
There are games that get storytelling right: Spec Ops: The Line (save for where it flops at the most crucial scene in plot development), Papo & Yo, Mass Effect (save for the hardcore flop in the third installment), The Elder Scrolls III: Morrowind, To The Moon.
There are games that get gameplay right: Supreme Commander, Just Cause 2, The Elder Scrolls V: Skyrim, later Call of Duty games.
There are games that get both correct. For whatever reason, I cannot seem to think of many good examples right now. Far Cry 3? Wolfenstein: The New Order? Star Wars: Knights of the Old Republic?
Typically, the games that are remember most are the ones that managed to balance a story with the gameplay and get the player involved. Those tend to be adventure games with player decisions affecting the storyline. Mass Effect tried really hard and succeeded with me pretty well. I never finished the first. I spent fifty-nine hours in Mass Effect 2, twelve in the first installment, and less in the third.
Our experience with video games are subjective. Each us will experience games differently than each other. Someone may like Papo & Yo, but I absolutely hated it and left the game several degrees of ticked. Someone may be moved by the game, but the only things I liked about it were the music and art. Nothing else. But that's what stories do. And famous game critics, like IGN, have been noted to mark a game down for having a heavy story.
I tried having a discussion with my English professor about Spec Ops: The Line and its tie-in with the classic literature novel Heart of Darkness and the movie Apocalypse Now. But that's hard to discuss. Movies are usually a couple hours, books can be read in a day or two, but video games are like virtual playgrounds. They can take up to several hundred hours to complete fully.
Some games like Guild Wars 2 have wonderful story telling mechanics. Guild Wars 2's story telling is remarkable (I'm told).
So what, as far as story-telling goes, strikes your fancy? Notable examples?
Have any experiences you'd like to share?
And if you were to write a game, how would you like to communicate the story?