Joseph, I use HDR heavily and constantly, and wouldn't want to live without it. If you search the image forums for past posts of mine you'll see what kind of stuff I do with it. Having said that, a couple of high level comments:
- First, IMO, HDR support in Photoshop sucks. I've had that opinion since my first jumbo HDR article published here at NSN years ago, and so far very little has happend in Photoshop to change my mind. Specifically, there are two parts of the HDR process -- merging individual exposures into a single HDR image, followed by tone-mapping the HDR image back down to a single TIFF (or JPEG) for finishing work. The latter is where Photoshop really falls down... its tone mapping capabilities are not that useful or interesting, for a given level of effort.
- Second, again IMO, to get really good looking HDR results is going to take more effort than simply feeding a bunch of files into some automatic software, and playing with a couple of sliders. I invest a fair amount of effort in my files. It took a lot longer early on because I had to experiment a lot and work around various deficiencies in the tools, as well as figure out where and how best to accomplish certain parts of the process to get the relatively seamless and photo-natural looking results I'm typically after. Now that I know how to do all that stuff, and with the tools having gotten better at handling larger files sizes and such, I'm much more efficient at working through this process. But for folks newly starting out, frankly it's probably going to feel like a pain in the butt. You have to really want that kind of result based on the shooting conditions and your desired resulting images. For anybody who doesn't want to invest that kind of effort in the post-processing area, use filters, go for artistic blown-out effects, or don't shoot in situations with high DR.

The majority of what I wrote ages ago in the
NSN HDR article is still valid, though the tools have been updated. I have also updated my workflow in a couple of key ways, often described in some of the longer technical / process bits in a few of my image posts. But start with the article; it will lay out the basics.
My HDR processing tool of choice has been and remains Photomatix. There are many more tools now than when I started with HDR, and some of them are getting interesting. But none of the newer ones have supplanted Photomatix as my workhorse. Most software is available on a trial basis so you don't have to shell out money to decide whether you really get a useful result or not.