To steer this thread back onto it's original topic, I thought I'd go through the great big list that Lexi gave and give my thoughts on each one. I'm a bit of an HL SP nut, so I know what I'm talking about!
Half-Quake: Amen - This, its prequel and its semi-sequel (HQ:Amen Present - it's another four levels or so, probably not worth your time tracking down) are very, very twisted. If I could sum up the series in one word, it'd be 'Sadism'. Surprisingly addictive though.
Afraid of Monsters + Director's Cut These were indeed pretty good. Get the Director's Cut - not only are the models and levels better, but there's four different endings. Have fun getting to them all!
Heart of Evil + Napalm Edition I thought the Napalm Edition was good, but flawed. Completely cutting out the HL2 section, for example, meant that the ending just felt awkward, and I wasn' a fan of the new voices. For maximum enjoyment, get both versions, then copy the new models from the Napalm Edition to the original one and away you go.
Cleaner's Adventures This was great with two exceptions. One - it was Russian (though I did work on an English patch some time back - really must finish that at some point). Two - the elevator glitch. Apart from that, this is an excellent mod.
Brave Brain It's another journey through Black Mesa. I don't remember much about it, so it can't have been particularly fantastic, but I've still got it on my hard drive, so it can't have been bad either. Worth getting (I think) but not essential.
Azure Sheep I don't get why this is so popular. The levels are poorly designed and the character of Kate gets very annoying very quickly.
Blbej den Never head of it, so won't comment further.
Case Closed Now this one I remember. Good stuff, if a tad short.
Life's End Ha, this one was just fun. Definitely had a sense of humour about it - I particularly liked the Half-Trek bit.
Cthulhu Now this was a very creepy TC. Pretty well made as well, though I remember that it got a bit tough near the end. Still good stuff.
Dead Sector IIRC, this was the mod full of headcrabs and
nothing but headcrabs. It did let me perfect the art of heabcrab-ball though - whacking them with your crowbar just as they leap at you. Very satisfying.
Escape from the Darkness - Another pretty good mod. Made the whole game a lot more realistic - you couldn't take many hits, weapon recoil is pretty heavy, that sort of thing. WTF ending though.
ETC Not really too heavy on plot, but it's a good showcase of mapping skills. Make sure you get the imaginatively titled sequel ETC 2 as well - that's even better.
Force of Evil Again, don't remember this one.
G-man House Not familiar with this one either.
Half-Life: Visitors It's a good little mod. Another 'Black Mesa Tale', but it's fairly well done. Not too sure about the last few maps, but otherwise it's a good one to grab.
Half-Life: Decay I actually managed to get through this all by myself. I'd never get to the bonus Xen level that way though.

Oh well. It's good stuff, even if it is hard to do on your own.
Half-Life: Red Alert If this is the one I think it is, then the title speaks for itself.
Invasion I couldn't get this to run properly - the subtitles and keypad things wouldn't show up for some reason. I think it was pretty good, but it's literally been years since I last played it all the way through.
Ispitatel I.-IV. Zany, if entertaining, mods. Bear in mind that #3 was never released due to the author losing all his work due to a hard drive crash.
Issues It's a veritable mish-mesh of maps, but they're relatively short and pretty enjoyable. I'll probably say the same about the others in the series as well.
Minimicus This was just fun. You know those multiplayer maps where you run around a giant house? Imagine a (short) single player mod like that.
Adam No. No no no. This was bad. Poorly designed levels, insta-kill traps, starting off by forcing you to break open a crate with a gun (and you have to pick the right one out of a dozen, with limited bullets) to find your suit... very, very bad. Stay away.
Mistake -1 This was a pretty weird TC. Felt more like FEAR then Half-Life. Pretty good, but it felt a little short.
Night at the Office Highly recommended. Pretty realistic, and has a great plot as well.
Nuclear Winter Great Opposing Force mod. Help save America after it's been nuked by the Russians! Hell yeah!
Operation Black Thunder HL mod that, oddly, feels like Opposing Force.It's good, if a little hard in places.
Operations 1942 A series of short missions set during World War 2. None are particularly easy, but all are satisfying.
The Gate NO. I replayed this recently (I am a sadist, after all) and couldn't get past the first stealth section. I kept getting seen right at the start. Useless.
P.I.Z.D.E.C. Don't know this one.
Paranoia Great fun. A well-made TC that looks gorgeous and has a genuinely creepy feel to it.
Point of View I found this much better then Azure Sheep, by the same team. The plot makes no sense at all, but it's great fun playing the 'bad guy' for once.
Poke 646 + Vendetta Absoluetly brilliant from start to finish and I implore everyone to play these.
Portrait of Freeman I vaguely remember this, and how it was alright, but not particularly special.
Redemption It's technically know as 'Absolute Redemption' now, but whatever. It's a good little mod with some excellent voicework. And Half-Park is just plain fun.
Residual Point Another 'Black Mesa Tale', but it's a good one. There's technically two different stories in this - be sure to select the Training Room to play the second.
Route City This one's OK. I guess. It's a nice idea, but the execution wasn't as entirely successful as it could have been.
Scientist Slaughterhouse Fun for a while, but you'll tire quickly.
Survive in Catacombs I. and II. I didn't play these for long - I never liked running around through tunnels and that. But they seemed alright.
Sweet Half-Life Makes no sense whatsoever and has the most infuriating boss fight I've played in years, but it's a fun little mod. Make sure you search for some replacement weapon models (well, you're in the right place!).
The Evil Thing Don't know this one.
The Gateway Don't know this one either.
The Trap Pretty challenging! A lot of good, if tricky, puzzles for you to make your way through. Mean ending though.
The Xeno Project I and II Not at all bad pair of mods. Makes Xen not too bad a place to play through, which is no mean feat.
They Hunger I.-III. Play it. Now.
Timefall A little bit of a oddity, but it's actually pretty good.
Timeline I.-III. Ah, those Nazis. Whatever shall we do with them? A good trilogy, but there's a lot of walking about in them, so be forewarned.
Times of Troubles Above average, but really should have been better.
Todesangst I. and II. Pretty funky pair of mods.
Twisted Hazard Course I tried, and gave up, on this mess of a mod. Especially in the area with 50 enemies that made my framerate die. Grr.
USS Darkstar Excellent mod set on board a scientific spaceship full of aliens who escape and... ah, just play it already.
Reissues It's a veritable mish-mesh of maps, but they're relatively short and pretty... wait, did I say this already?