NWN's main campaign is pretty good, I think, primarily because it educates you in just about every facet of Dungeons and Dragons gameplay. Mods vary from absolute crap to absolute gold, but almost none of them will give you the variety of experiences that the main campaign will. The story's not bad, and the gameplay's not bad either. The only real problem with it is that it can be fairly difficult if you want to play solo on harder difficulty modes (1st-level wizard = dead man walking), and since it's mostly linear there's not much replayability to it.
Shadows of Undrentide is even more linear, which can actually be kind of funny if you intentionally play a sociopathic character who treats everyone like crap but is expected to save the world anyway. Again, it's not bad, but not as good as the main campaign.
Hordes of the Underdark, being a more-or-less direct sequel to SoU, is more of the same at a higher level. The story is a tad more epic though, and it's significantly less linear in that you can get multiple endings depending on what choices you make at key points in the game.