The kind of game I love best is the one that lets me tell my own story - or one that helps me build a new one.
Most games tell a story, of course. However, it is pre-written, with the player simply going through the motions of the narrative. If the game designers have done their job, "going through the motions" is a lot of fun - take Half-Life, for instance.
Then there are those games which give the player some control over the minutia or even the outcome of the story. Deux Ex is a favorite, though the "multiple endings" part was pretty weak. However, through your actions you could definitely affect various game events.
I have yet to play a great game where the player can have truly dramatic impact on the story itself; Understandably, as this kind of feature would be a massive undertaking as the chaos effect takes over - each choice begets more choices (branches within branches within branches). Still, it would be fun to see it pulled off.
More and more, though, I find that my favorite games are all ones which have no built-in narrative at all, but they serve very well for letting me create my own. Recently (well, in the last few months) I've been playing the hell out of a game called Crusader Kings II. This is a grand strategy game partially in the vein of Europa Universalis (same studio, I think) - however, the "personal politics" portion of the game has been expanded dramatically, and it is beautiful. You can start as Holy Roman Emperor or as a lowly count, and from there you try to expand your lands, accrue titles, keep your vassals in line, and avoid conquest by rival powers. Arrange political marriages, grow the prestige of your family dynasty, install friendly claimants upon various thrones, etc etc.
My first game saw me take the reigns of Hungary in the year 1066. I conquered parts of Croatia, then was stomped into the dust by the Ottomans. However, my character was not relieved of my ducal titles, so I still ruled a duchy within the Ottoman Empire. I navigated the politics of this foreign conqueror successfully enough, and the senior branch of the Árpád dynasty adopted Egyptian culture. Later on I sabotaged the internal politics of the Empire (yes, you can do that) enough so that I and a number of other territories declared our independence. I went on to reconquer Hungary's old territory and reclaim the crown, and then some. By the time the game ended in 1450, Hungary was one of the preeminent kingdoms in the world, controlling both her traditional lands as well as most of Croatia, Serbia, and parts of the Balkans - including, in the closing years, claiming Constantinople in a Crusade and creating the Latin Empire.
Phew. Now that was a helluva story (no doubt more fun played than retold in brief, but you get the point).
And that's only one game. Plenty of others exist and give totally different experiences and types of stories to cook up. Mount and Blade and Dwarf Fortress are another couple of favorites. Dwarf Fortress, especially, is famous for delivering some of the most hilarious and twisted stories gaming has to offer.