Hmmm, OK here goes:
Warcraft games (not WoW) - good campaigns, but more importantly great map editors. I've made two 30 level campaigns for WC3 (one for Reign of Chaos and one for The Frozen Throne). You can imagine that might have kept me pretty darn occupied, but before that I had loads of fun with Warcraft 2 Tides of Darkness and its expansion Beyond the Dark Portal and also some fun with the first Warcraft.
Starcraft games - similar reason, but mostly applies to the first Starcraft, made a large campaign for that one too. I'm currently making a SC2 map, kid of a self-contained story with original characters that tries to fit into the overall plot of SC2. Unlike other Blizzard RTS games, I doubt I'll make much more than this one map.
Dragon Age Origins - finished it six times, completionist playthroughs. Awakening expansion (two playthroughs out of six) and DLC included. 'Nuff said.
Hitman series - especially Silent Assassin and Blood Money. Awesome games that let you approach the mission the way you want to and kill your target(s) in a lot of interesting ways.
KoTOR series - was there ever any doubt these games took a nice chunk of my free time?
Jedi Outcast/Jedi Academy - as mur'phon said, these are still the best force-powered Star Wars games out there. And they have a bunch of interesting mods (Although I was mostly focused on additional Multiplayer characters and stuff like that).
Heroes of Might and Magic III - I just loved playing this one as a kid. Me and a friend would often play Hot-Seat matches against each other. Still the best Heroes game in terms of gameplay. The newer ones look great, but they have lost a lot of that HoMM charm.
Legacy of Kain series - story, story, atmosphere and some more story. Oh, and great voice actors all around. The gameplay was pretty good too, but that story... oh, man.
VtM Bloodlines - finished it at least once with each clan. Aside from Legacy of Kain games, this is the best vampire game out there. Great, wacky story and cool, wacky characters.
There might be a couple more, but that's all I can think of right now anyway.