HDR is not all that new in the mainstream. The photographers with whom I interact have been using it for years. HDR is a tool for a specific purpose and it is also a tool to create an effect.
As a tool, it is designed to compensate for scenes which exceed the capability of the camera's sensor to capture detail in both the highlights and the shadows. It has a place and when used subtly, it can compensate for your sensor's inability to duplicate the sensitivity of your eyes. Current generations of DSLRs already exceed the range of film. Images shot with my Canon 40D (which has a much greater dynamic range than my previous DSLRs) have the ability (through proper RAW processing) to retain highlight details and reveal shadow details on very dynamic images, blowing away slide film and soundly beating negative film. It still has a long way to go to meet the capability of the human eye.
As a special effect, like any other special effect, it can quickly become trite. Just like grundge, partial black and white, duotones, blurs and other previous 'in' effects, it will run its course. Already, it becomes wearisome to my eyes. I can look at an image and usually it is obvious what was done. Some photographer's style is already defined by HDR and every image they turn out looks like it was over processed in HDR. As an effect, it is art. Art is subjective. If you like it (and more importantly your clients do) then use it. If it suites your artistic vision use it.
Photomatix at www.hdrsoft.com creates, in my opinion, the standard in HDR software. PSP X2 merge to HDR and Photoshop CS3 HDR capabilities are tinkertoys by comparison. If you are going to take the plunge, I would recommend it above all others.
As a judge in photographic competitions, I am wary of images that exhibit a heavy hand in HDR. As I review competition images obvious appliction of HDR is a negative-subtle use can be a positive if warranted. The degree of artistry required by the competition does cause me to slide my perception slightly.
Is it worth it. Yes. There are times when it will save an image and make an impact. Will you go to far with it? Undoubtedly. Can you learn to control this power and channel it for good? Like all effects, absolutely.