There is absolutely a limit, though that limit varies from individual to individual widely. The most classic example of this variance is women and childbirth. It's commonly said that a man couldn't undertake childbirth without passing out.
The point where this happens is really part of our genetic history. If you think about it, we can ask the question "why would we pass out from trauma." Typically being conscious is better than not being conscious. However, we note that there are situations where we may make short-term rash decisions which have long term implications (like opening wounds and bleeding to death). If our genetic heritage doesn't trust us with one of these decisions, it will engage whatever mechanisms are involved to make us pass out.
The effect of this can be fatal if or genetic heritage was wrong. I do believe this is why, in trauma situations, we tell the individual to stay awake. In many such situations, the conscious mind is doing a better job of maintaining homeostasis than our backup unconscious approaches will. However, once you're in the operating room, and a black man is about to stab you with a knife, they give you drugs to put you unconscious so that you don't resist (the knife is a scalpel, and black people can be surgeons too -- especially when it adds to the deceptive shock value of my word choice! Remember, while you may be conscious at that point, you're not very conscious. The scene you think you see may be as extreme as my racial stereotype! ).