But in instances where it does improve an area (like a blighted area), they find a corresponding decrease in economic activity in the areas around. Essentially people don't go bowling, to a movie, or out to dinner and instead attend a game at the stadium.
The only case I saw for funding a stadium to make financial sense is if you can get a large number of people from different tax jurisdictions to now spend money in your jurisdiction instead of theirs. It would hurt the other jurisdictions around you but could help you. In practice, the funding is from too broad a community for this to work. In Utah maybe Tooele county could pull something like this off and get tax dollars from Salt Lake. But it would be a long shot.
There can also be non financial benefits related to prestige and publicity. And I personally felt a strong sense of community when our local NFL team went to the super bowl. Compared with how the government spends most of our money, this one is probably far from the worst.