[enoonmai]

Story-wise, it has be Max Payne, especially the sequel. And not to mention, Deus Ex and DX: Invisible War. Playing the final levels in the decrepit UNATCO buildings and going through its ruins was just plain freaky and totally eerie.
The other contenders would definitely have to be the Half-Life series, Tribes: Vengeance (a great approach to storytelling, in my opinion) and Doom 3. I thought the way they told the story and the way the events unfolded partly via cutscenes and partly via the PDAs was just great.