Stadium subsidies are a classic example of failure to appreciate "what it not seen." As Dennis Coates and Brad Humphreys point out in this survey of the literature, subsidies for stadiums and big events don't do much for a local economy. When you add in the costs of lobbying and schmoozing for subsidies, government-funded stadiums are almost surely a net drag on the local economy.
Stadium enthusiasts nonetheless might fall back on "civic pride" as a reason to have a stadium as having a Home Town Team might make the city a better place to live. If this were the case, it should be reflected in real estate prices, and I'm not aware of work that has looked at this. Second, there are already other possible providers of sports-related "civic pride." In Birmingham, for example, we have a new, subsidized downtown stadium for the Birmingham Barons even though colleges and universities like Samford, Birmingham-Southern, Miles, and UAB already provide lots of options for sports fans and, presumably, lots of opportunities to build civic pride and social capital.
But don't take my word for it. Here are Nick Gillespie and JC Bradbury with more.