I've had both shoulders done, and I'll share with you the good, the bad, and the ugly.
If you have your shoulder done arthroscopically, post-op pain is minimal. I had to have one shoulder done fully open, due to the extent of the damage, and I have to tell you: That was rough. I've got a high pain tolerance, and it was still bad.
In most cases, rotator surgery is now done outpatient, but your first few days, you'll need some help, including possibly getting dressed, so if you have a friend or family member who can stay with you, that can be really helpful. The pain meds and lingering effects of anesthesia also can leave you pretty out of it, so don't plan on doing a lot for the next few days.
You'll be in sling for several weeks, but your surgeon may have you start passive PT very soon. It's extremely important that you do this, as the more stretching you do, the better your ultimate range of motion. If you do everything by the book, you will only lose 1-2 inches range of motion.
Two other very important things: Full healing can take as long as three years due to limited blood supply in the tendons themselves. It is vital that you not overdo things or have a second tear, as there may not be enough tissue to repair things a second time, which can result in a situation like John McCain's, where he has limited use of his arms, or worse. I know that not being able to lift or have a serious workout just sucks, but if you want to get better, do recovery right.
Also, wear your sling if you are in a situation where you could re-injure your shoulder. For instance, if you have to be outside on icy surfaces, better to roll if you falls, versus instinctively putting your arm out to break the fall.
The good news is you will eventually be able to resume sports and working out, and the pain relief is worth it.
One final bit of advice: Vet your surgeon carefully. This is fairly complex surgery, and you want someone who will do things right. Even better if your surgeon also does sports medicine, as she or he can help you map out a plan to safely resume sports, etc.
I hope this doesn't discourage you--I've just tried to give you the best, most accurate information I can, with the goal of helping you get better and having realistic expectations.