Morning, OTT.
Some other games that had a great story: Planescape: Torment (as Sneaky pointed out), Baldurs Gate, Knights of the Old Republic, Fallout, The Witcher, Phantasy Star.
Sure, they are all RPG's and I guess you really can't delve too deep into a story with a 7-9 hour FPS...Deus Ex being the exception, but that had Role Playing elements to it. IMO, BioShock managed to pull it off.
I guess a lot of it has to do with the dialogue and the interactions between characters. Nobody did it better than early BioWare.