Well you need to understand that NWN is more than just a single game or story. Yes, there is an official campaign that comes with the game, but more importantly is the toolset which allows for players to create their own games using the NWN engine. There are over
2000 user-made modules at NWN Vault, which can be freely downloaded by you and played as either single or multiplayer. These modules are rated and categorized for your convenience. Check out some of the modules that are listed as having
high role-playing content and see if any of those interest you. The official NWN saga may or may not interest you, but you should be able to find pleanty of juicy modules at the Vault to satisfy you.
I think a lot of the people who don't like NWN dislike it because they have issues with the game play, interface, combat style, 3rd edition D&D rules, etc. But if you like the NWN engine, then you should definately get this game because new user-made and even some offical content is coming out at a very nice rate. It's the game that keeps on giving! How do you know if you'll like NWN? Well you can now download the NWN demo at Bioware's site which might give you some insight into the game play and give you a taste of what's to come. A better indication might be to read over some of the threads at the official Bioware NWN forums and see what people like/dislike about this game and determine if NWN is for you. If you are expecting another Baldur's Gate, then you'll probably be disappointed because in my opinion the experience you will get from NWN is nothing like BG. They both have their pluses and minuses, but in the end they are both good rpg's that offer different role-playing experiences.
Links:
http://nwvault.ign.com/Files/modules/categories/
http://nwn.bioware.com/