I'd imagine sports are a field where a fear of failure can definitely help, because fear narrows your focus considerably, and allows you to concentrate on the pretty narrow aspects that lead to success in sports.

I benefited from something very similar in college where a fear of doing poorly helped me focus right before exams, allowing me to do really well in most classes (especially the ones where the grades were heavily weighted towards assignments and exams).

OTOH, this left me completely unprepared for work life after college, where a lot of my work is dependent on collaborating with others, and in general requires a much broader way of engaging my tasks (sometimes the best way to solve a problem is to solve the "real" problem without ever having to tackle the problem you were supposed to be working on at all). And fear certainly doesn't help with that.

I think a combination of working for positive reasons (especially when considering the longer term) and working because of negative reasons (more beneficial on specific tasks in the short term) is probably what works best in most endeavors.