Of the modern titles Mass Effect 1 + 2 and Dragon Age are pretty spiffing.
If you're willing to go classic there's a whole heap:
Grim Fandango
The Longest Journey
Knights of the Old Republic 1 + 2
Nexus: The Jupiter Incident
Beyond Good and Evil
Broken Sword 1 + 2
Baldur's Gate 1 + 2
Warcraft 3 (yes a RTS with an awesome storyline!)
Starcraft 1 (another RTS, sadly 2 had a pretty naff storyline imo)
Metal Gear Solid (i think this had a PC release? If not, emulators ahoy!)
Fallout 1 + 2 (3 is too open world and as a result i feel the central storyline suffers)
As just a taster, those will last you months. I'll brainstorm for some more. I still maintain that Final Fantasy 7, 8 and 10 have the finest storylines in gaming, mainly due to excellent character development (the end of 8 and 10 were a little flakey but i'll let them off due to the characters). Sadly 10 was never released on the PC, just the PS2.