I think you should try some of my favorite FPS games, identity, because I only love real good story based FPS games too.
Let me list the ones I've played and beat. And in most cases, played again and beat again. These are the FPS games I spent LOTS of time with, and really fell in love with each one, so this is my list of gold.. if you care.
Deus Ex: The Conspiracy, as mentioned above.. Also the sequel is good too I'm sure (own it but haven't played)
Red Faction (the original game. 2 was terrible, don't bother playing it. guerrilla is cool, but i haven't played it)
Perfect Dark - this game was Goldeneye007's little brother, and kicked much more ass in my opinion. The story drove this game so well, and the multiplayer was ridiculously fun too. I downloaded it to my xbox and its still classic.
Killer 7 (one of the coolest games ever, especially fps)
Psi-Ops: The Mindgate Conspiracy
Second Sight
Half-Life (was cool, but for some reason I didn't find any replay value)
somebody mentioned Timesplitters. The story was ok, I had more fun with multiplayer.
And of course, the original
Halo, which you seem to be less satisfied with ... I'll admit, I didn't see much to the Halo series (as you didn't) but upon playing the campgains, I found a higher love. Let me also take this moment to say that the story in Halo one is really good; Halo 2, 3, and ODST were terrible. But Reach is going to be awesome.
And more recently, games that I'm playing that I haven't beaten yet... Fallout3 [is absolutely breathtaking], Battlefield BC2 [its okay], AVP [really cool, but sometimes bland], Mass Effect, and finally, one that I think will be on my top list of all time best fps games once I finish it: Dead Space.
Hope that helps. I have to end this unnecessarily long post by saying that I enjoy games a hundred times better when the story is good, so I'm not arguing game vs game vs game, just arguing that a game without a good plot is as dead to me as goldeneye became in its dying days. which BY THE WAY forgot to mention, goldeneye 007 is an awesome classic.
OK i'll leave