What happens depends on the source of the problem. If a problem occurs in a high quality power supply it should just shut down. In a cheap, low budget, low quality power supply of questionable value and performance a problem in the psu might also fry a cpu, motherboard, or other components. In a worst case scenario it may actually start a fire. An external problem such as a lightning strike can be just as bad.
Here's a link to a video clip showing what can happen:http://www.youtube.com/watch?v=JP8nFQYQW34
The absolutely worst thing that can happen is a bad psu starting a house fire. There are a couple of video clips showing the damage.