I have read a few accounts from people who had some problems during this procedure, but really, I think they are a very small minority. I was terrified before I had mine back in December, but it turned out to be, as they say, "no big deal." I wanted to be anesthetized for the whole darn thing, but was told, no they could not, because I might be asked to respond to the invasive cardiologist's requests during the procedure (things like, turn your head, take a deep breath, etc.). However they generally do sedate you.
The procedure itself is NOT painful at all. (And believe me, I am a wimp when it comes to pain.) The most I felt was a slight sting as the needle went into my groin area and even that was not bad as you are numbed ahead of time. I also felt a slight warm rush when they injected the dye. After that I don't remember feeling a thing, although I was conscious the whole time. The cath lab does tend to be very cold, so ask them ahead of time for some warm blankets if you are sensitive to cold.
As some have said, the worst part is the period afterwards. You have to lie still (on your back) for a certain number of hours. In my case it was the full six hours. I know some places now use the "plug" system so you can leave early, but not everywhere. Really, even the six hours is not too bad if you are prepared. You can't sit up & read so it is a good idea to bring a CD player, MP3 or some other device so you can listen to music or talking books or whatever. Or ask them to give you some more sedation during the post-procedure time, and just try to go to sleep.