¯¯¯¯¯¯¯¯¯¯
Here are a couple of RPGs and related adventure games I've tried and can recommend:
==============================================
1) Deus Ex, of course. It's effectively a first-person near-future science fiction Half-Life-like version of NWN, with lots of obvious D&D influence. It's another stop-the-plague story told via the Unreal engine, with in-game cutscenes and conversations. There's only a single character class to start, but experience points can be distributed amongst different skills and weapons proficiencies, and the nanobot canisters you find as the game progresses provide ability choices, followed by upgrade enhancements of these choices. This makes for a myriad different specialization possibilities.
Excellent plot. One very good plot twist. Different actions on your part results in different plot paths, conversations, and consequences down the line. There are even three different endings. Very good gameplay, with a bit of replayability because there are so many different ways to accomplish an objective.
This is one of the top five games I've ever played. I am frankly quite surprised you haven't tried this one.
==============================================
2) The Wheel of Time. Third-person pseudo-RPG based in Robert Jordan's WoT universe. You play the part of Elayna Sedai, a Brown with almost no channeling ability, who's Keeper status is resented by some in the White Tower. It's all here: Trollocs, Myrdraal, the Children of the Light, Shadar Logoth, its killing tendrils AND its Hound, the Ways Between and the evil that lives there, even Ishamael the Forsaken and those spiffy cuendillar seals. Guess what? You wind up having to save the universe.
Excellent use of the Unreal engine, but awkward predefined-hotkey inventory management. I recall some frustration in battle encounters with choosing the appropriate offensive/defensive weapon from the 40 types of ter'angreal. Balefire's hot, though. ;-)
One of the game's levels is actually a game type called Citadel, where you have to fortify a prison with trap and manpower deployment to protect the prisoners from an impending attack. This game is based on one of the multiplayer game types.
There's a key plot revelation about midway that stuns the player, but makes the ending cutscene the perfect denouement. It made me feel like Robert Jordan had scripted the plot.
==============================================
3) Outcast. This near-future (to start) adventure game has a simplified automatic skill point distribution based on what you do, and cooperation from the locals is based on your reputation -- even item pricing is affected, if I remember correctly -- so be nice and do the side quests. The major plot twist in this game is one of the best I've encountered, evoking a visceral feeling that I've had only once since, while watching "The Sixth Sense".
The voxel-based graphics engine is cool, but limited in resolution. You have in-game cutscenes, and an in-game choice of third-person and first-person view, great for ranged weapon usage. Quests are somewhat tedious in that the solutions are quite scattered in this very large world. Only eight or ten save-game slots.
==============================================
4) Anachronox. "Far" future third-person party-based RPG. Uses the Quake II engine with a couple of nice enhancements like a great particle system. There was more humor in this game than in all the other games I've ever played _combined_. Excellent unfolding plot spans three expansion-contraction cycles of the universe. Pure RPG turn-based battles. Minor plot twist just before the final battle.
Interesting spell setup: Differently-colored beetles can be collected and aligned on a hexagonal grid to build powerful offensive and defensive "spells". Normal gameplay is interrupted (as part of the plot) by one of a series of mini-games you have to complete to move on. They were not to my personal taste.
It has more concrete problems, though. Many of the bugs were fixed in updates, but shame on Eidos for forcing Ion Storm Dallas to release it before it was ready (it was very late). There's a poor design decision to prevent game saves during one long, long, LONG series of heavy-duty battles just before the final encounter. My party died the death of a thousand cuts, almost a thousand times.
==============================================
5) System Shock 2. Relatively near future RPG/FPS, full of a constant eerie tension punctuated by some real surprises. Three character classes (unbalanced; see below) to choose from. The grid-based inventory management was a pain. Weapons deteriorated very rapidly and could jam at any time, a game design flaw that Looking Glass owned up to by issuing a patch to reduce the decay rate, and turn it off if you wanted.
I didn't finish this game; I trained in psionics (equivalent to a sorcerer/wizard) and there was one place in the game where I could only die because I didn't have the firepower to take out a particular turret. Later I learned that most play the game as a weapons expert (e.g., fighter) because it doesn't have the weaknesses of the tech cil (thief) or the psionicist. I might try again, later.
==============================================
Except for System Shock 2, these games have a plot I rate at nine or better, on a scale of one to ten.
¯¯¯¯¯¯¯¯¯¯
Since then, I've played very few games where the story enthralled me. I highly recommend Psychonauts, a console port of a platformer that I actually liked (and you cannot fathom the depths of my hatred for console ports and platformers!). You play as a character seemingly modeled on roles that Michael J. Fox would play. Your friends have literally lost their minds, and you have to help them get those minds back. It's extremely funny and clever, with very creative level design (10/10). "T V!"

If you get this game, get the patch that fixes the camera viewpoint in the butcher's circus level to avoid frustration.
¯¯¯¯¯¯¯¯¯¯
Okay, if you like your games to have a good story: What did you play that you liked for plot or story?