Heavy Rain takes the top spot for me. MGS4 and Valkyria Chronicles tie for second.

Crisis Core: Final Fantasy VII takes the top spot.

Tales of Vesperia, Final Fantasy XIII and Metal Gear Solid 4 had good stories too in my opinion.

MGS4

Mass effect1&2, God of War 3, bioshock were all great too.

My Top 6:

1) God of War III
2) Alan Wake
3) Bioshock
4) Mass Effect
5) Castlevania: Lords of Shadow
6) Gears of War

In no particular order:

*SPOILERS AHOY!!*

-Braid: I don't really know what was going on in that game. All I know is that when I finally made it to the princess and found out that the guy who kidnapped her was actually saving her from me, I sat there in awe for quite a while after I finished the game. Mind blowing.

-Red Dead Redemption: John Marsden has to be the most well rounded character in all of gaming. He seemed like somebody that could actually exist. A former outlaw being forced to hunt down his former gang. But it's not about the character, is it? It's about the story. His epic tale that began with him being gunned down, to his fighting for Mexican liberation, to finally finishing his quest--only to be killed by his superiors--it's gotta be up there as one of gamings best.

-Enslaved: I loved this game at first sight and I love it more, today. A girl tasks a man with keeping her alive on her journey home. If for some reason, she dies, he will die a painful death. The story plays out in a familiar way but the way the characters seem so....human. That's what I love about it. When Trip finally sets Monkey free from the headwear that would kill him and he asks her, "What the hell are you doing? Put it back on!", I got chills. The journey, the relationship between these two (and eventually a third person), and the powerful conclusion of their journey- What can I say? I love this game.

-Heavy Rain: The story of the Origami Killer takes several surprising twist and turns as it changes to the point of view of four unique protagonists. The exciting moments put an exclamation point on the game but it's the quiet moments that get me. The relationship between a father and a son. The mystery of who the killer was. The question, "How far will you go for someone you love?" There's no other game this gen like Heavy Rain. The story alone is worth the price of admission.

Honorable mentions go to:
-Mass Effect (Story hasn't ended yet)
-Gears of War (Yes, the story is awesome but it's not over until later this month)
-Batman Arkham Asylum (not the most creative but it had me hooked from the start of the game to the end)
-God of War 3 (I went from hating the series and the main character to liking it quite a bit--that's one hell of an accomplishment).

Heavy Rain

MGS4 story = a presentation based around nanomachines in a glorified manner.

For me it's Assassin's Creed 2. CoD4 (surprisingly) had a good one too.

Either The World Ends With You or Bioshock

The Darkness

Silent Hill: Shattered Memories.