There can only be a purpose for sex if there is a purpose for life, which means that sex (or any other activity) can only have ultimate meaning if God exists.  If there is no God, then all of life is ultimately meaningless.

Since God exists, the main purpose of sex is to bond a man and woman together to procreate and raise children.  But isn’t pleasure a purpose for sex?  Certainly, the Bible speaks highly of sexual pleasure (see the Song of Solomon).  But the pleasure we experience during sex encourages us to bond with one another and procreate.  In other words, pleasure is more the result than the purpose.  If pleasure is the primary purpose of sex– not bonding or procreating– then we would have to say that pleasure should be pursued even if it harms.  Professor J. Budziszewski explains in this conversation excerpted from his book Ask Me Anything.

“The main point of Christian sexual morality is that human nature is designed. We need to live a certain way because we’re designed to live that way.”

“Then let’s start with the heart. Do you see how every part works together toward its purpose, its function?”

“Sure. You’ve got nerves and valves and pumping chambers, all for moving blood.”

“Right. If you think about the sexual powers instead of the heart, it’s just the same. The key to understanding a design is to recognize its purposes. For the heart, the purpose is pumping blood; for the sexual powers—you tell me.”
“Pleasure?”

“Think about it. Would you say pleasure is the purpose of eating?”

“No, I’d say nourishment is the purpose of eating, and pleasure is just the result.”

“If you thought pleasure was the purpose of eating, what would you do if I offered you pleasant-tasting poison?”

“Eat it.”

“And what would happen?”

“I’d get sick.”

“But if you understood that nourishment is the purpose of eating and pleasure merely the result, then what would you do if I offered you pleasant-tasting poison?”

“Refuse it and ask for food instead.”

“It’s the same with the sexual powers. Pleasure is a result of their use, but it’s not the purpose of their use. The purposes can tell you which kinds of sexual activity are good and which aren’t; by itself, pleasure can’t. The inbuilt purpose of the sexual powers is to bond a man with a woman and the other is to have and raise children.”  (HT: Jim Whiddon)