Been comming to this site a fair while and haven't felt the need to post until now as no-one seems to have mentioned Deus Ex!
Deus Ex is probably the greatest story heavy game I have played and remains my favorite game of all time. The story is rich and twisting and you have multiple outcomes dependent on your actions.
I think others that need a mention are:
Final Fantasy VII
MGS series
Half-Life series
God of War series
Mass Effect
Star Wars: KoToR
Bioshock
Seriosuly though, if you haven't played Deus Ex, go buy it!