CoD 4 is probably one of the better FPS campaign for PC. Black Ops had a pretty good tale to tell as well I must admit. BC2 was good for the characters.
For really good campaigns you're probably better off with games that are dedicated to telling a story. Doom 3 comes to mind, really good campaign for it's time.
The Halo's have a good campaign but can be pretty dull at times. But there length and lore makes up for it.
But the best of the best is definitely the Half Life series. Nothing comes close to the personal tale of Gordon Freeman and his allies.
Also Bioshock and Bioshock 2 are definitely campaigns worth trying. Sometimes they are repetitive and predictable but the characters portrayed are really well portrayed. Can't wait for Bioshock Infinite.