Tough for me cause I don't play as many RPGs as most people here. I'll do the ones I can think of.

Kingdom Hearts. The first game has it all for me. It's sweet, sad, fun, and unlike the other entries in the series is basically a self contained game. I think you would benefit most from playing it at a certain age, because it could be perceived as quite childish I guess, but I think that's something that is easy to set aside. I just love the game so much and I wouldn't be able to play it as many times as I have if the story wasn't great.

KotOR2 for basically the same reasons vyk mentioned. It's probably the most immersed I've been in my individual character in an rpg. My love of Star Wars as a universe and actively 'wanting' to get lost in it has a lot to do with this, but it still facilitated this desire better than any game up to that point.

Mass Effect 2. One word: Gripping. From the moment it starts to the conclusion it sets such a fantastic and epic scenario. Getting your old crew together and building up to the suicide mission is really engaging.

That's all I can think of for now but I'll add more as I think about it.