Agreed on Knights of the Old Republic. That was the game which drew me in the most. I ended up playing through the whole game 4 times.
Zelda: Ocarina of Time is another one that drew me into the story.
However, I have never drawn the same emotional impact from a game as I have from really good movies.