The only RPG that held my attention on story alone was Xenogears, and I didn't even think it had a good story when I was playing it and to this day I have no idea why I DID finish that game.
The thing with stories in games, it's like... Games are an interactive narrative, so on one hand I really like games where I can control the narrative or... no, not what I mean... Games where my ACTIONS shape the narrative. Like Crusader Kings -- you have a personal character in the game, and he'll pick up different traits or grow in different ways depending on what you do, and I feel a lot of connectedness to my in game dynasty because of that.
And Boatmurdered is sort of a canonical example of gameplay as narrative.
On the other hand I DO like more traditional stories in games, although I think I prefer... more non-linear stories that I can explore myself. Sort of.
I guess what hooks me in a story, though, is... when a story gets me thinking about its possibilities, beyond what's necessarily being told, and then what keeps me is the quality of writing, and the detail.