In reality, each has its own level of prestige. Military sniping implies creeping along in the low crawl poistion for hours to sit in the mud for hours to wait even more hours (or days) in the rain, snow, blistering heat, freezing cold, etc., to take a single shot at a distant target.
LE sniping, on the other hand, entails laying in the prone position watching every movement of a terrorist and his hostage, waiting for the order to fire. If you miss, you could stand before a court trying to explain why you killed an innocent hostage. In addition, your every movement is being recorded by tv cameras from across the nation.
Even though these descriptions are dramatized, they do get the point across. If you want to become a sniper because it would be something "cool", then you may be doing it for the wrong reasons. But if you are willing to dedicate youself to it completely, then the world needs more people like you! Only you can decide which catagory you belong in.
It would be good to "test the waters" befor jumping into something and finding out it's not really what you wanted. There are many military-type schools you can go to to see if this career is right for you.