I think the economics vary with the sport.
I don't think football works at all. The big parking lots used for very few dates does not work. I infinitely prefer the model Bob Kraft ended up taking, with some limited infrastucture improvements, private capital for the stadium, and the opportunity for Kraft to grab any synergy by owning and developing the surrounding land. He gets to use the lots for Patriot Place when the Pats aren't playing. he bought the land himself. It's not like Angelos asking the state to buy the land for him so he can make money. Frankly, do that, he should get no money for the stadium since he profits off the development.
I do think both Nats Park and the Gallery Place arena (MCI/Verizon/Cap 1) probably catalyzed some of the development. Whether it was going to happen regardless is another question. Largo ended up finding an alternate use, so the Arena in particular was a good thing. I think if you have a baseball park or both hockey and hoops in an arena, at least the facility is used 80+ nights per year. I think a two team arena is a bit better an investment than a baseball park just because there can be more concerts and other uses like college sports.
There are definitely transformative developments but 90% of stadium deals now are simply cash grabs but the richest people alive, and the bootlickers who think they're "good deal for the city" are just rubes. A baseball stadium in Nashville might help develop a new neighborhood, but it also probably was getting developed anyways, and the people benefitting the most are the developers, landlords who sell and friends of the owners who get fat, fat contracts. A demo-and-rebuild football stadium does nothing except make the rich get richer, the poor stay poor and the fans pay more.
The soccer stadium in Nashville was privately funded with the city taking the cost of infrastructure around it and it's definitely helped the neighborhood a ton! But you know what is great: the city is only paying for things the whole city can benefit from, not a new owner's suite.