A lot of what I'm seeing here is mostly just "games with A LOT of writing", not strictly well-written games. The Mass Effect universe gets mentioned way more than it deserves - I adore the games, I love the characters, and the setting is well-conceived, but the main story is as generic as space opera gets. The dialogue is good, sure, and the ME2 loyalty missions are the pinnacle of it all, but the context of the second game is entirely clumsy and uninspired. Basically, they reboot everything, re-introduce generic-humanoid-alien-cannon-fodder antagonists, and end the whole thing with another status quo. It felt like a Voyager episode, and you cannot afford a filler when you're making a pre-planned trilogy, otherwise everything important will be anticlimactically stuffed into the third part.
Dragon Age had the same stellar BioWare characters, and a main plot that's typical, but very functional (kinda what ME1 had), and it had a much less rigid structure than ME or KotOR, so you couldn't entirely predict the entire game as soon as you enter that "choose quest locations in whatever order" part. The thing that bugs me with newer BioWare games is that they feel a bit too mechanical; there is almost an audible "clunk!" when you finish a mission, and you're back in camp/on the ship and everyone's dialogue resets so you can continue to interrogate them. This makes NPCs kinda like story dispensers.
In any case, I'd say Planescape: Torment; not because it has a lot of writing, but because it manages to be meaningful and humorous at the same time. The story isn't exacly light-hearted, and deals with rather problematic issues, but it manages to keep it witty and tongue-in-cheek throughout. The game is actually a morbid comedy.
But I subscribe to the "less is more" school of thought, so I prefer videogames where the writing is visible in the design, and doesn't include only character interaction, but also world-building, lore, building up tension, creating an atmosphere. You don't need dialogue if you get these things right (for example, Ico has a wonderful story, even if it's barely there).
EDIT: Oh, right, Silent Hill 4 probably has the best story in the series. Too bad the game isn't all that fun to play, and pulls that "the first half of the game in reverse" crap.