I'll stick with series instead of sole games, and assume story encompasses character development, environment, and some interesting dialogue content:
Mass Effect (so what else is new? very few sci-fi works for me like this does)
Legacy of Kain (character development, and philosophical themes ftw)
Mafia (one of the most powerful stories ever written in any media)
Honorable mentions: Max Payne (deep stuff, great monologues), Longest Journey (amazing worlds), Bioshock (not so much the story per se, but the whole environment and concept behind Rapture)
and then there's the kotors, dragon age, the broken swords, HL2, Portal, Strife (anyone remember this?

) etc etc...
edit: oh, and beneath a steel sky! Not only a great story, but Dave Gibbons artwork ftw!