Hmm. Morrowind has probably done this the most, and is the only game I've ever felt homesick for when I was on holiday and unable to play. Not feeling homesick for my rl home, or simply wanting to play it, but feeling homesick for the locations in the game. It really drew me in, in a way which the later TES games (which I actually played before Morrowind, so it's not a case of nostalgia) failed to do.
The 'most recent' game which has immersed me in its story, oddly enough, is Eador. Not a game you'd think of from the point of view of a story, but that's because the story arises from your actions rather than being told to you as you play. A very good thing, in a computer game.
I may also end up adding Baldur's Gate to my list, but as I'm still playing it (game is open as I type) I can't say for sure.
The other game which has kept me thinking and worrying about it even now, over a year after I finished playing, was Bastion. I think I made the wrong decision at the end...