Bioshock, Mass Effect, Half-Life, Portal, Oblivion, and Fallout. I am not a big fan of JRPGs, but I thought Lost Odyssey had a very interesting story.
The game that really hit me with the story was the Witcher. The game was loooong, but they did with the story what I think all of the other games that pretend to offer choice were trying to do. You know the big choice in Mass Effect? Well in the Witcher, every decision you make results in unforeseen consequences, with lives on the line, and there is never a best option. The story was epic, because it put the player in situations with NO good choices, but you still had to pick one and live with the consequences.