Ah, the old Zero Sum argument. (Long discredited I might add)
Many projects DO pay for themselves, Petco / Safeco / PacBell, etc. (I can't say much about Football stadiums, many of those are dual use in order to generate enough revenue.
There are examples (Safco) where this has worked very well, and others where it has failed miserably. The devil is in the details.
The State of New Jersey has been going through various iterations of this process in recent years to rationalize spending a pile of taxpayers' cash on a new arena for the New Jersey Nets (NBA) and Devils (NHL). The numbers often show that "arena will pay itself off in X years," but when you look at them in more detail you start to get a good picture of just how easy it is to manipulate the numbers.
One of the ways they do this is by including the income tax revenue from the team's players, front office staff, etc. in the "benefit" side of the equation. That might be technically correct from a financial standpoint, but you never see that rationale used in any other context. Using this argument, I could go out tomorrow and justify spending all sorts of taxpayer money in the area where I live -- simply because this is a pretty nice area of the New York City region and most people living here pay in the highest state income tax brackets.
P.S. The "zero sum" argument may have been discredited, but it is often valid when applied to specific cases. A family that spends $250 on a night out at an NBA game is spending $250 that they would have had regardless of whether the NBA team was there or not.
Nope. The simple fact is that people have a certain entertainment budget, and if they don't spend it on sports they spend it on something else. Thus, stadiums only redistribute a fixed pie.