It started with 19th century artistic depictions of the Vikings, particularly the Ring Cycle by Wagner, so the myth can basically be attributed to artistic license. There are examples of helmets with hornlike protrusions (look up the Waterloo Helmet.) but these are too thin to be considered battle armor, and so were probably ceremonial.

as to the point of effectiveness in battle, most helmets designed for combat favor deflecting the blow off the helmet, so the majority of the force does not go into your head. a Horn or similar protrusion creates a corner a weapon can catch which could give you a severe head or neck injury when your helmet is abruptly yanked in the direction of the blow.