What scenarios are better shot with a prime lens? A zoom lens? A macro lens?
Prime lenses are better suited to specific environments - a 50mm f/1.8 is great for food photography, a 100mm macro is quite flattering for portraiture, but neither work well as a general purpose "carry-round" lens. I use Canon's 28-135 IS lens as a carry round, and can't sing its praises higher, even performing well doing some music photography the other week.
My definitions follow...
Prime: fixed-length lens. Shorter ones are good for portraits, while longer primes are better distance shooters than the zoom varieties. You have to use your feet to get the right setup.
Zoom: lens can zoom in and out. Can be versatile, depending on the lens. Tends to have lower performance than prime lenses.
Macro: for closeups of small objects, and sometimes portraits. Not for distance shooting.
Whether to use primes or zooms is largely a matter of personal preference. That said, zooms are usually considered to be more versatile and better suited for fast-moving environments--i.e., photojournalism, sports, etc--since you can probably achieve your desired framing more quickly by zooming than by running or changing lenses. When speed is not an issue--say, when making art--primes are great, usually having advantages in size, maximum aperture, and price/performance ratio. Fast primes are a huge asset in low-light situations where flash is not allowed (e.g., performing arts), and/or when small depth-of-field is desirable (e.g., portraiture).
As for macro lenses, well, most true macro lenses are primes. Macro lenses are best for macro (i.e., close-up) photography, but there are macro lenses that also happen to be great walk-around lenses, such as the highly-regarded Pentax DA 35 f2.8.
I use prime lenses for portraits (e.g. Canon 50mm 1.8), zoom lenses for traveling/holidays and my 100mm macro for well... macro shots :).
I use a Sigma 17-70mm F2.8-F4.5 DC most as it is most versatile. But prime lenses are usually sharper and allow a shorter DOF which is preferrable for portraits.
The correct prime lens will always give the better photos, however owning and transporting 101 prime lenses is not an option for most people, hence a zoom lens is more practical as a main lens.
The other partial problem with prime lenses is that every time you change a lens you risk letting dust in.
The first step to getting a great photo, is to be there with your kit, so ask yourself:
If I use a lot of prime lenses will I be likely to take less photos?
A reason to select a prime lens, is that for the money, one normally gets a bigger aperture, which lets in more light, or has a more limited depth of field. A second reason prime lenses are used is that for the cost, mass and size they product better image quality with mid-apertures.
More light is helpful in limited lighting, like in a church, where flash will not be used.
Limited depth of field is often desired for outdoor portraits, and other shots where the background or possibly the foreground (and possible also the background) is softly out of focus. Different lenses have different patterning and characteristics and this is sometimes classified under the term bokeh.
The limited depth of field is more pronounced with lenses with f-stops of 1.2 or 1.4, and much more limited with zoom lenses which may have a lower f-stop of 3.5.
In each of these instances, it is necessary to use the lens more wide open, as opposed to a mid-aperture such as 5.6, 8, or 11. However prime lenses used in this range have other advantages. Because the optics has fewer system trades (like being able to run with a focal length of 18, and 300, all in the same lens, it is easier to design and build a higher quality lens. So the MTF of a given prime will normally be better than a comparable priced zoom or varifocal lens. The advantage here is that one gets crisp images, and the lens is smaller and lighter and may have less secondary artifacts such as lens flare, than a comparable zoom lens.
A macro lens is one which permits close focus. Historically it meant a lens where the image area to capture sensor (including film) was 1:1, but that is much more liberally construed these days. Normally a macro lens will not focus to infinity. Before the days of autofocus lenses, VR, metering and the like, we used to unscrew the lens, reverse it, and hand hold it between the camera and the scene for a macro shot. Some shorter (focal length) lenses may have near macro ability to close focus.
Also, for some macro lens applications, it may be viable to use filter like lenses with typical diopter values of +1, +2, +4, and +10. They can be stacked, and they change the focal length of the lens and are handy to take pictures of bugs, flowers and things a macro lens might be used for. Usually they are not coated, and are primitive lenses, with chromatic aberrations, lower MTF, and other adverse artifacts. A set is very inexpensive. Other devices and strategies exist for macro photography,