Some of the following games were very emotional at certain points in the story while some were emotionally up and down through most of game.
I'll try to list them by "has affected me most at the top and descending".
Mass Effect
(The ending mainly, but the whole atmosphere of the game was amazing)
Half-Life 2: Episode Two
(if you've played it, you know exactly what I'm talking about.)
The Darkness
(This game is one of the best and most sad games I've ever played, the storytelling really is a masterpiece.)
Dark Messiah of Might and Magic
(Xana and Leanna, 'nuff said)
Half-Life 2
(Alyx really made this game what it is)
Half-Life 2: Episode one
(Same as above.)
Mass Effect 2
(with the inclusion of Lair of the Shadowbroker, that should tell you enough.)
Star Wars: Knights of the Old Republic
(Bastila Shan, and Biowares storytelling. You all know what I'm talking about)
Baldur's Gate 2
(This is storytelling on a whole other level.)
Saint's Row 2
(I really bonded with my female protagonist, and dispite this games unserious and comedic setting, it has a few very serious moments that provides a huge contrast to the rest of the game, increasing their emotional impact ten-fold.)
Dragon Age 2
(I liked the characters in this game a lot better than the previous one, with the exception of Morrigan and Alistair.)
Dragon Age
(I can't really point to one specific event in the game, but how could you not be affected by the sheer epicness of the story?)
Prey
(This game is so underappreciated it's sad. Seriously, try it if you haven't already)
Dead Space
(Strangely enough, of all the emotions felt when playing this game, fear wasn't one of them. Though I can't help but feel sorry for poor Issac.)
Age of Conan
(The musical score in this game is UNBELIVABLY good. Seriously.
-
Those are just from the top of my head. Oh, and I forgot...
Thief 3: Deadly Shadows.
(Shalebridge Cradle.)If you've played it you know what I'm talking about, if you haven't, DON'T!