At least one game (NetHack) sets up the framework for a story but lets randomness fill in 99% of the details. It seems like everyone in the last 12 years has tried to steal bits of NetHack for their mass-market game, with mixed success. (Diablo comes to mind. Spore, too - bones files->Will Wright's "massively singleplayer" game.)
NetHack is a good example of emergent gameplay, Daggerfall is another. They do not, however, have compelling stories. All fan-fiction to the contrary, "I kill the kobold and take his chainmail leggings" fifty times in a row doesn't constitute a real plot.
Also, what story does exist is by virtue of how limited the player's interaction with his environment is: he can either kill things or carry them. If his actions were less discrete, or lacked real-world metaphors, there wouldn't even be a fan-fiction plot.
To be fair to nethack, you can not only kill things, you can make them your pet, appease them with gifts, use them defensively in a variety of ways, or frighten them off with bright lights or elven scribblings. And, as for things you can do with objects...I'm not even gonna try.
But, yes, what "story" there is is not emergent, and is injected by human authors, and the game can only really have two outcomes: death (about 95% of the time) or ascension and success (with a few bits of story line along the way based on your character class, race, and alignment). The joy is in the many amusing ways in which your character can die, rather than in the narrative.
