As with every issue, there are going to be exceptions, but in a decent amount of cases, public sport subsidies which allow for sports facilities to be developed will typically result in increased revenue for the immediate area.
For example, in Concord, NC, the NASCAR facilities were developed out of pocket and repaid by the county. This resulted in a high loss initially, but now has repaid dividends by creating opportunity for many camping venues, food and beverage providers, general goods providers, merchandisers, as well as the additional tourism revenue that results from the three major NASCAR events every year.
Generally speaking, if a sports franchise can stay successful, and create additional outside opportunity, the benefit is sustainable.
These subsidies almost never pay for themselves. Not to mention most of the money generated for the stadiums go to the people with corporate positions. Also, people who may not even like sports are forced to pay for them, when their money could go o funding schools or other local agencies. Lastly, when stadiums are built, people are forced from their homes because of eminent domain. Even though they are given "just compensation", the government decides what is just and many of the people who are displaced do not have the money to challenge these compensations, even though they have the right to under the Fifth Amendment.