Yes NWN is most definitely great fun for single player gamers. Multiplayer is even better, but the OC and SoU and HotU campaigns are all great fun whether enjoyed alone or with friends.
Think of it as a modular game. It comes with 3 great adventure stories for one or more players, plus you can download any number of additional stories. The cool thing is the downloadable stuff at NWVault is created by real people, with at the very least 70+ adventures of extremely high quality. The downloadable adventures have a huge, huge range in scope and tone, with genres ranging from from sci-fi TCs to high fantasy to dark fantasy to sketchy stuff, and then finally the criminally sketchy stuff, which is apparently popular. [not sure about wordfilters around here] In any case It's a vast field of options to suit your taste.
And yes, the OC story is not better than BG2 but it is plenty interesting and fun, with a variety of characters and interesting subplots (The Coldwood trial as someone mentioned above was especially compelling, as is the lawyer quest later on) and is easily on par with, say, BG1 or Icewind Dale. Plus the sheer fun of NWN gameplay (yes it is more fun to play than the BG engine) ups the enjoyment. And playing HotU is grand, so epic you will get chills from the very first cutscene to the very last one. Keep Deekin and Nathyrra in your party--they have a lot of different conversations to share.
Above all however, no matter the adventure module you are using, singleplayer is great, but NWN is best played with friends, either with your computer DMing or a live DM.