Half life series, all of them, even blue shift opposing force etc...
All have a great story. HL2 too of course.
I find Bioshock very very cool, the scripted bits. It's not a ''great shooter'', but the feeel you have in it, the environment, and the story make a real special and awesome game... It looks very pretty too with eye candy maxed and AA forced on...
You've played most other superb games...
What was it you didn't like about bioshock demo ? The game is filled with nice semi creepy bits like when you exit the capsule at start, or the nice old style music etc... It has very very very cool cinematics later on too. I would go as far tbh that the cinematic/scripted bits in bioshock are far cooler than HL2 scripted bits...
Devil may cry 4 has a good story imo, but it's a pretty basic hack and slash game best played on a joypad, if you li3ek console ish games then get this...
And as someone mentioned:
Mafia has such an awesome story, looks dated these days perhaps but very playable and well, you had to have played it tbh...
Have you played/tryed the GTA series?
If not, then really get vice city then san andreas... They're very cheap now but still have superb story/missions...
I personally prefer SA but most people like the story of VC more, which is awesome too...