Apparently, yeah, that's what Handel's Royal Fireworks Music calls for. Plus, of course, a contrabassoon. And a serpent. Or so says some tuba dissertation I downloaded, Wikipedia doesn't mention the serpent. Still, that's a lot of bassoons.
The piece itself sounds like fun. I guess 18th c audiences liked pyro just as much as modern ones. The first performance was a blast; it burned down the specially built concert hall. Here's a modern performance on period instruments, I love the tall baroque contra, and the drum line of tympani. I don't see a serpent though.
For modern pyro, here's a couple examples. Rammstein's act includes a lot of fire effects. I can't imagine trying to play under the conditions onstage: notice the sheet metal firewall in front of the keyboards, trying to protect the electronics from the heat. All that fire looks pretty dramatic, though. If you like this sort of thing, their song Rammstein puts their singer in an H.R. Geigeresque costume which also includes flamethrowers.
A more poppy example (well, euro trance perhaps) is Cascada. A little less pyro than Rammstein, and a totally different esthetic. What strikes me about both of these groups is the relentless focus on the show, on the audience's experience. They are both about telling a story, evoking an emotional response, and they use every tool at their disposal in the service of their show.